VBA Challenge

[SnowyBry]

Hi all, have recently joined the Forum after learning so much here over the past few months so thanks to all contributors!

I'm new to VBA but have been getting a lot of success from existing threads however I'm struggling to find a solution for my current VBA task... if anyone is up for the challenge, any help will be much appreciated.

The summary is...
I have a main folder which contains named folders relating to each team member. Within those folders are weekly timing spreadsheets, all with a similar name but has the date of the relevant working week.

What I need is a separate monthly total workbook (let's call it "Monthly Totals") which is able to extract the relevant data from each of the team members spreadsheets for each week of the month.

So far, I have created the Monthly Total spreadsheet which has a tab for each team member and then a tab which sums up the total for the entire team. I have entered formulas on each tab which automatically generates the folder path (Cell A1) and then I have several cells which generate the file name for each weeks spreadsheet (Cells D2, G2, J2 & M2). All tabs have the same layout.

The data I need to extract is from a hidden tab ("Figures") on each of the weekly spreadsheets and then need the values pasted to the relevant range.

Below is how I visualise the process and I'm now struggling to get the relevant code to perform the process...

• Open filename (D2) found in folder (A1)
• Unhide "Figures"
• Copy D4:F39
• Switch to Monthly Totals workbook
• Paste Values in B4:D39
• Close Weekly spreadsheet without saving
• Open filename (G2) in folder (A1)
• Unhide "Figures"
• Copy D4:F39
• Switch to Monthly Totals workbook
• Paste Values in E4:G39
• Close Weekly spreadsheet without saving
• Open filename (J2) in folder (A1)
• Unhide "Figures"
• Copy D4:F39
• Switch to Monthly Totals workbook
• Paste Values in H4:J39
• Close Weekly spreadsheet without saving
• Open filename (M2) in folder (A1)
• Unhide "Figures"
• Copy D4:F39
• Switch to Monthly Totals workbook
• Paste Values in K4:M39
• Close Weekly spreadsheet without saving

I then need the above repeated for every named tab excluding the "Totals".

(On a side note, I may need add an additional cycle for when there's five weeks in a month)

Would this actually be possible or is there an alternative method for collating/extracting data from multiple data sources that I'm not currently thinking of?

Thanks again!

 

[NdNoviceHlp]

Hi *SnowyBry and Welcome to the Board! Pretty much anything is possible with VBA and what you're after is definitely doable. Start by coding to open 1 wb, unhiding the sheet and then copy and pasting to your other wb. However, I would suggest just setting a range of your desired data and the moving the data by using Resize.... there is no need to copy and paste. If you search my previous posts, there's quite a few that move data from 1 wb to another using Resize. Which brings up how to learn VBA, copy and paste code snippets and learn how they work... they're free. The macro recorder is also somewhat helpful if you can learn how to strip all the garbage out of the code. One other VBA friend of mine is the Msgbox. Learn how to use it to display code results when testing. If you have specific coding difficulties, post the code here and outline your difficulty and I'm sure someone will help you out. Good luck. Dave

 

[SnowyBry]

Thank you the reply NdNoviceHlp, Resize is a new one to me so will take a look at some of the other threads on that.