Macro query + excel 2003

[Neil Holden]

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

 

[Jacob Skaria]

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

 

[Neil Holden]

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]

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")

 

[Neil Holden]

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]

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

 

[Neil Holden]

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]

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

 

[Neil Holden]

hi 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?