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

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