Autofill same cells but with multiple worksheets

[rickcb]

I have 52 identical worksheets (one for every week) and need to create a quick copy of each worksheets' C9:J11 cells. The autofill doesn't quite understand what I'm trying to do as it keeps referencing the same worksheet when I drag the handle across the grid.

Any shortcuts to this or will I have to do this manually ?

Thanks

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F8)

Sheets.Add.Name = "Summary"
For a = 1 To Sheets.Count
Cells(a * 3, 1) = Worksheets.Name
For b = 3 To 10
For c = 9 To 11
Cells(a * 3 + c - 9,b) = Worksheets(a).Cells(c, b)
Next c
Next b
Next a

run the macro. It will add a sheet called summary and collates all C9 to J11 in it.
Ravi

 

[rickcb]

Information missing

I've tried to run the macro but I get a compile error. I forgot to mention that the worksheets are all named as WK 1, WK 2, WK 3,... WK 52

Thanks

 

[ravishankar]

Hi
No need for sheet names in this macro. I found an error in the code I gave. The modified code is

Sheets.Add.Name = "Summary"
For a = 1 To Sheets.Count
Cells(a * 3, 1) = Worksheets(a).Name
For b = 3 To 10
For c = 9 To 11
Cells(a * 3 + c - 9, b) = Worksheets(a).Cells(c, b)
Next c
Next b
Next a

Ravi

 

[rickcb]

Thanks, it works fine