GroupTrackerMaster.xlsm is my source workbook with a fixed range that will be copied and pasted to many destination workbooks (one of which is GroupTrackerClientTemplate.xlsm). I'm trying to piece together vba that will loop through all the target workbooks in a specific folder and run a macro "CopyMastertoAll". This macro needs to paste the copied range from the source to the target workbooks with two criteria:
Week# - A drop list in the source workbook will be user defined between 1-12; this will determine which worksheet to copy to.
Day# - A drop list in the source workbook will be user defined between 1-3; this will determine where on the worksheet to copy to.
eg. Week4, day2 will paste data to Sheet(Week4) and cell range (B29:G44)
I tried to find bits of code for the different components of this code, but the migraine is getting painful. Here is what I have so far (lots is missing):
This is the last code I need to launch my program; any help is greatly appreciated!
Week# - A drop list in the source workbook will be user defined between 1-12; this will determine which worksheet to copy to.
Day# - A drop list in the source workbook will be user defined between 1-3; this will determine where on the worksheet to copy to.
eg. Week4, day2 will paste data to Sheet(Week4) and cell range (B29:G44)
I tried to find bits of code for the different components of this code, but the migraine is getting painful. Here is what I have so far (lots is missing):
Code:
Sub Export_Master_To_Client()
Call RecursiveFolders("/Users/MORFITOffice/Dropbox/Small_Group_Training/Workouts/Clients")
End Sub
Sub RecursiveFolders(ByVal MyPath As String)
Dim FileSys As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim objFile As Object
Dim wkbOpen As Workbook
Set FileSys = CreateObject("Scripting.FileSystemObject")
Set objFolder = FileSys.GetFolder(MyPath)
Application.ScreenUpdating = False
For Each objSubFolder In objFolder.SubFolders
For Each objFile In objSubFolder.Files
Set wkbOpen = Workbooks.Open(Filename:=objFile)
Call CopyMastertoAll
wkbOpen.Close savechanges:=True
Next
Call RecursiveFolders(objSubFolder.Path)
Next
Application.ScreenUpdating = True
End Sub
Sub CopyMastertoAll()
Dim wkbkSource As Workbook
Dim wkbkClient As Workbook
Dim wsSource As Worksheet
Dim wsDestin1 As Worksheet
Dim wsDestin2 As Worksheet
Dim wsDestin3 As Worksheet
Dim wsDestin4 As Worksheet
Dim wsDestin5 As Worksheet
Dim wsDestin6 As Worksheet
Dim wsDestin7 As Worksheet
Dim wsDestin8 As Worksheet
Dim wsDestin9 As Worksheet
Dim wsDestin10 As Worksheet
Dim wsDestin11 As Worksheet
Dim wsDestin12 As Worksheet
Dim rngSource As Range
Dim rngCel As Range
Set wkbkSource = Workbooks("GroupTrackerMaster.xlsm")
Set wkbkClient = ActiveWorkbook
Set wsSource = wkbkSource.Sheets("Program")
Set wsDestin1 = wkbkClient.Sheets("Week1")
Set wsDestin2 = wkbkClient.Sheets("Week2")
Set wsDestin3 = wkbkClient.Sheets("Week3")
Set wsDestin4 = wkbkClient.Sheets("Week4")
Set wsDestin5 = wkbkClient.Sheets("Week5")
Set wsDestin6 = wkbkClient.Sheets("Week6")
Set wsDestin7 = wkbkClient.Sheets("Week7")
Set wsDestin8 = wkbkClient.Sheets("Week8")
Set wsDestin9 = wkbkClient.Sheets("Week9")
Set wsDestin10 = wkbkClient.Sheets("Week10")
Set wsDestin11 = wkbkClient.Sheets("Week11")
Set wsDestin12 = wkbkClient.Sheets("Week12")