Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a table from one workbook to ALL worksheets in a different wb
Hello Excel Gurus!
I need to copy a summary table that is in workbook "A" to all worksheets contained in workbbok "B". Sometimes workbook "B" have 800+ worksheets. So I tried to make a macro that would that for me as follows: Windows("A.xlsm").Activate Range("A1:B17").Select Selection.Copy Windows("B.xls").Activate Dim Wks As Worksheet For Each Wks In ActiveWorkbook.Worksheets With Wks Range("B31").Select ActiveSheet.Paste End With Next Wks Windows("A.xlsm").Activate Range("D1").Select However it is not working properly since it is not changing to the next worksheet in the workbook. Can you help me with it please? By the way, worksheet's number are not consecutive numbers, even some of them have been renamed to a word like "Store1". Thanks you in advance for any and all help! JB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a table from one workbook to ALL worksheets in a different wb
I'd use something like:
Dim FromRng as range dim wks as worksheet with workbooks("a.xlsm").worksheets("What's the name of the worksheet") set fromrng = .range("A1:b17") end with with workbooks("b.xls") 'is that the correct name/extension for each wks in .worksheets fromrng.copy _ destination:=wks.range("b31") next wks end with Notice the dots in front of those ranges (and objects)--that means that they belong to the object in the previous With statement. JABAgdl wrote: Hello Excel Gurus! I need to copy a summary table that is in workbook "A" to all worksheets contained in workbbok "B". Sometimes workbook "B" have 800+ worksheets. So I tried to make a macro that would that for me as follows: Windows("A.xlsm").Activate Range("A1:B17").Select Selection.Copy Windows("B.xls").Activate Dim Wks As Worksheet For Each Wks In ActiveWorkbook.Worksheets With Wks Range("B31").Select ActiveSheet.Paste End With Next Wks Windows("A.xlsm").Activate Range("D1").Select However it is not working properly since it is not changing to the next worksheet in the workbook. Can you help me with it please? By the way, worksheet's number are not consecutive numbers, even some of them have been renamed to a word like "Store1". Thanks you in advance for any and all help! JB -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy all worksheets to another workbook, excl. duplicate sheets already in other workbook | Excel Programming | |||
Copy worksheets to a different workbook | Excel Programming | |||
Copy worksheets within a workbook | Excel Discussion (Misc queries) | |||
Copy Worksheets from one Workbook to Another | Excel Worksheet Functions | |||
Copy four worksheets from one workbook into a new workbook.e-mail | Excel Programming |