| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro query + excel 2003
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro query + excel 2003
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? "Jacob Skaria" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| importing/linking data from an Access 2003 Query to an Excel 2003 | Excel Discussion (Misc queries) | |||
| Excel 2003 SQL-Query | Excel Worksheet Functions | |||
| Web Query (Excel 2003) | Excel Worksheet Functions | |||
| MS Query and Excel 2003 | Excel Discussion (Misc queries) | |||
| New web query with Excel Pro Edition 2003 | Links and Linking in Excel | |||
Linear Mode
