Thread: how to copy data from one excel sheet to another automatically?

Hi,

Yes, it's a common task. Does the worksheet you want to copy from always have the same name? In which case you can hard code the name in the macro, otherwise you'll either need to prompt the user for a name or read the name from a cell in the workbook into which you want to copy data

The general format of such a macro, assuming the workbook name is fixes is:

Code:

Sub CopyWorkbookData

Dim wbMyBook as Workbook
set wbMyBook = ActiveWorkbook
Workbooks.Open("MyWorkbook.xls")

ActiveWorkbook.Sheets("MySheet").Range("A1:Z100").Copy Destination:=wbMyBook.Sheets("Summary").Range("A1")
ActiveWorkbook.Close

You can do it very easily by using VBA. The changing filename is not an issue if both sheets are in the same file, so I guess thay are not. That being so, if you can put the active VBA code into the source file, and refer to self as ThisWorkbook, actual filename does not then matter. If you want to 'grab' (rather than send) it is a little trickier. Working on the first assumption for now, copy this to a code module:

Code:

Sub Macro1()
   ThisWorkbook.Worksheets("Sheet1").Activate 'Source data sheet
   Dest = "TargetBook.xls" 'Must be open
   For n = 5 To 17 'As per example
       data = ActiveSheet.Cells(n, 7) 'G5 to G17
       r = 1
       Do While Workbooks(Dest).Worksheets("Sheet2").Cells(r, 1) <> data 'looking for match
           r = r + 1 'next row
       Loop 'Note - this will give overflow error if data not found in Column A
       Workbooks(Dest).Worksheets("Sheet2").Cells(r, n) = data 'do it
   Next n
End Sub

Try this hope this helps.
Good Luck