Loop through workbooks and paste range based on source value

Discostu

New Member
Joined
Sep 2, 2018
Messages
2
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):

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")
This is the last code I need to launch my program; any help is greatly appreciated!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top