I need help copying data to the next cells

[Kinger1968]

I'm very much a novice at this and have been scouring the internet trying to work out how to do it.
I have a workbook that has worksheets called Week1, Week 2 etc up to Week 52, if I go all year.
They are generated on a weekly basis and I'd like to take 4 cells of data (K11:K14) from each sheet every week and put them in a table on a sheet called "Graphs."
The cells I want to put the data in start at (C8:C:11) on Week1 and Week2 they move to (D8:D11), which is where I'm failing.
I have this code below so far that only puts data in Week1. Every time I try to add loops to index the destination cells I get all sorts of errors.

Sub WeeksLoop()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Menu" And ws.Name <> "Graphs" And ws.Name <> "Master" Then

Worksheets("Week1").Range("K11:K14").Copy Destination:=Worksheets("Graphs").Range("C8:C11")

End If

Next ws

Application.ScreenUpdating = True

End Sub

Any help would be greatly appreciated!

Thanks in advance.

 

[BigBeachBananas]

Would you run the macro once you have all 52 weeks or routinely?

 

[Kinger1968]

Would you run the macro once you have all 52 weeks or routinely?

Routinely. I'd like to be able to press a button and get the data from existing pages, whatever week number I'm on.

 

[Kinger1968]

I have now cracked the problem

 

[jolivanes]

It would be nice for people that are searching for a solution with the same problem to have your way of solving the problem.

 

[Kinger1968]

I will post the exact code I found to work when I get back to work.

 

[Kinger1968]

It could probably be a lot better, but it works!
Sub GraphData()

'Set variables

Dim WS As Worksheet, iCnt As Long

Dim counter As Long

Dim column As Long

Dim sheet As Long

'Disable screen updating to stop flickering

Application.ScreenUpdating = False

'Count all sheets with "week" in the name and record the result in a variable

For Each WS In ActiveWorkbook.Worksheets

If LCase(WS.Name) Like "week#*" Then iCnt = iCnt + 1

Next

'Set variables for the name of the starting sheet and the column of the destination cells

Let sheet = 4

Let column = 3

'Create loop to cycle through all the sheets with "weeks" in the name

For counter = 1 To CStr(iCnt)

Worksheets(sheet).Activate

'Set source cells which are the same on every sheet

Worksheets(sheet).Range("K12:K15").Copy

Worksheets("Graphs").Activate

'Set destination cells which move to the right each loop

Worksheets("Graphs").Range(Cells(8, column), Cells(11, column)).PasteSpecial

'Index the name of the starting sheet and the column of the destination cells by 1

Let sheet = sheet + 1

Let column = column + 1

Next

'Re-enable screen updating

Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

Thanks for posting the code. For the future, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.