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


--
Jacob


"Neil Holden" wrote:

Jacob your good!!

Is it possible to have a sheet where I had sheet names for example:

Test 1
Test 2
Test 3
Test 4

And then when the button is pressed it looks for that columns that I have
added and transfers all the sheet name information to that external workbook?

You have been a massive help, much appreciated.

Neil

"Jacob Skaria" wrote:

Hi Neil

Check out the below

Sub Macro()

Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet, lngRow As Long

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

lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("Sheet1").Range("A21:S81").Copy ws.Range("A" & lngRow)
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
wb1.Sheets("Sheet2").Range("A21:S81").Copy ws.Range("A" & lngRow)

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

End Sub

--
Jacob


"Neil Holden" wrote:

I'm with you now! Thanks. One last change, can i make it so that each sheet
finds the first available row and keeps adding the information onto the
external sheet?

At the moment if i press the button if overights all the previous info?

Thanks Jacob your a star.



Jacob Skaria" wrote:

The below line of code copies the data from Sheet2 of activeworkbook to
Sheet2 of external workbook.....

wb1.Sheets("Sheet2").Range("A21:S81").Copy wb2.Sheets("Sheet2").Range("A1")


--
Jacob


"Neil Holden" wrote:

Hi Jacob, thanks for your help, however this only copies over sheet 1 data
and not sheet 1 and 2?

Please assist.

Thanks for all your help!!!

"Jacob Skaria" wrote:

Hi Neil

Try the below macro..

--Edit the extternal workbook name and path
--Edit the destination sheet name...
--Edit the copy desitnation. Currently that is mentioned as cell A1

Sub Macro()

Dim wb1 As Workbook, wb2 As Workbook

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("c:\1.xls")
wb1.Sheets("Sheet1").Range("A21:S81").Copy wb2.Sheets("Sheet1").Range("A1")
wb1.Sheets("Sheet2").Range("A21:S81").Copy wb2.Sheets("Sheet2").Range("A1")
wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


--
Jacob


"Neil Holden" wrote:

I need a button so that when pressed it exports the data from the cell range
A21:S81 on this sheet it also exports the info from the same range A21:S81 on
sheet 2 to an external excel workbook.

Thanks