Consolidate Information of Multiple Worksheets Into a Table

[JasonTSG]

Hello,

I am a powerlifting coach for numerous gold, silver, and bronze national medalists in the USA, Canada, and Asia. It will help me a tremendous amount if a forum member could find a solution to this problem I am facing.

My Goal

Click a button which copies analytics information from a "microcycle" of training into a consolidated table. There are 52 microcycles of training in a year, so there will be 52 microcycles of training in my document.

My Problem

When I click the button, it copies and pastes the values of ALL microcycles of training. I only would like it to copy and paste the values from the LATEST week of training.

Sub CopyRawData()


For Each cell In Range("WorksheetsToCopy")
Worksheets(cell.Text).Range("Z6:AT6").Copy
Range("NewRawData").PasteSpecial xlPasteValues


Next


EndSub

***Range "WorksheetsToCopy" contains Microcycle 1, Microcycle 2... Microcycle 52.***

Please help!

Jason Tremblay
President of The Strength Guys Inc.