Copy Data From One Workbook To Another Workbook

Copy Data From One Workbook To Another Workbook

For This ,both the workbooks is in the same directory or  it can be  in other directory ,
Let us-  Source excel sheet name is "Example.xlsx" and sheet name is  "OscarTorres" .
then firstly we have to find out the location(path) of this workbook. and similarly doing this with another one, where we want  to copy the data. Below I write the simple example  for this, here  is the excel sheet is in the same directory.

Requirement:-

1. We need two worksheet(it may be perform with multiple worksheet).

2. Define the rang.

3. Find out the path of workbook location.

We Can Write this code with module and assign to Form Control Button,and  another way is write in ActiveX Controls Command Button.

Go through this-  Excel Sheet --->Developer Tab--->Insert---> Form Controls---> Button

Here I create one 'Micro' ,and it can be assign to button or run indusualy.

Sub CopyData()

Application.DisplayFullScreen = False

Dim SourceWorkBookName As String

Dim SourceSheetName As String

Dim SourceWorkBook As Excel.Workbook

Dim SourceSheet As Excel.Worksheet

Dim workingDirectory  As String

SourceWorkBookName = "Example.xlsx"

SourceSheetName = "OscarTorres"

workingDirectory = Application.ActiveWorkbook.Path

Set SourceWorkBook = Workbooks.Open(workingDirectory & "\" & SourceWorkBookName)

'ActiveWindow.Visible = False

Set SourceWorkBook = Application.Workbooks(SourceWorkBookName)

Set SourceSheet = SourceWorkBook.Worksheets(SourceSheetName)

 

ThisWorkbook.Sheets(1).Range("A1", "A43").Value = SourceSheet.Range("AE531", "AE573").Value

SourceWorkBook.Close

Application.DisplayFullScreen = True

End Sub

Describe About Ranges With Last Row In Excel Sheet

Describe About Different  Type  Of  Ranges With Last Row In Excel Sheet:-

Last Row For Any  Column Or Row:-

Let us we define LastRow is as a integer and need  to caculate the last row of  perticulre column for range in sheet. Here is  some syntax for last row with discription.

  

1. LastRow = ActiveSheet.UsedRange.Rows.Count

It give the value of used  number of current sheet's row . 

2. LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

It also give the value of used  number of current sheet's row with the search direction(From where is the used last row ) like a  xlPrevious, xlAfter, xlNext.

3. LastRow = Range("A65536").End(xlUp).Row

It give directly the value of  rows in specified column(Here is "A65536") .

4. LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

It  simply calculate the last cell in column or  row (Here we write row ).

5. LastRow = Sheet1.Coluumn(2).Find("*",,,,xlByRows,xlPrevious).Row

From this code we can find out last row with respect  to  column index and  same as explain in 2 method  written in above.

Range For Excel Sheet With Last Row:-




1. This syntax is for simply known range for user,here is "xlwbook" is name of 

sheet and "rng" is excel range and  A2 to B2 is range.

rng=xlwbook.Range("A2","B2")

2.This syntax is for find out last row in perticulre column(here is "A") ,it 

select whole rows of column A. 

Workbooks().Sheets().Cells(Rows.Count, "A").End(xlUp).Row

3.If we want to find or calculate last row according to our need. We can Write the range as:-

  rng=xlwbook.Range("A2","B2"& LastRow)   

Here is " LastRow" is gives just  number of  rows in Column.