Macro Help CONT 2003
 

Not sure if you got this reply Jacob?

i have created 4 sheets and a test sheet.

In the test sheet I have a list of the 4 sheets to copy?

Is this correct?


Neil, try the below

Sub Macro()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, lngRow As Long, intCount as Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("c:\1.xls")
Set ws = wb2.Sheets("Sheet1")

For intCount = 1 to 4
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("Test " & intCount).Range("A21:S81").Copy ws.Range("A" & lngRow+1)
Next

wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Macro Help CONT 2003
 

Hi Neil

No. The below code actually add (copy) data from A21:S81 of sheets 'Test 1',
'Test 2' etc; of the active workbook to an external workbook Sheet1.

--
Jacob


"Neil Holden" wrote:

Not sure if you got this reply Jacob?

i have created 4 sheets and a test sheet.

In the test sheet I have a list of the 4 sheets to copy?

Is this correct?


Neil, try the below

Sub Macro()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, lngRow As Long, intCount as Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("c:\1.xls")
Set ws = wb2.Sheets("Sheet1")

For intCount = 1 to 4
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("Test " & intCount).Range("A21:S81").Copy ws.Range("A" & lngRow+1)
Next

wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub