Copy data from workbook, paste into another template, and repeat for multiple files

maneet5

New Member
Joined
Apr 7, 2014
Messages
8
I tried to look for solutions for this. I was able to find stuff on copy data from multiple files into one, but what I want to do is copy a range of data from a workbook, paste it into a template, save as a new file, and then repeat for more workbooks in a folder. Here are some things that may help clarify:


  • I have x number of files in one folder (50-100) in path C:\Users\M\Desktop\Baseline.
  • I need to open a workbook that has a name like 12340101_1D7B6F_6_8-30_8_2012_MODELX.csv
  • I need to copy data from A20:last cell with data in column C in this workbook
  • I need to open a template sheet called "processing_template.xlsx" and then paste that data into cell A2.
  • Finally I need to save the template sheet as the same name (minus the .csv) as the original plus _processed e.g. 12340101_1D7B6F_6_8-30_8_2012_MODELX_processed

I kinda sorta tried to tweak some code and resulted in this, but I'm having an issue finding a way to call the "processing_template" in a way that it will actually save and then loop to the next file.

Code:
Sub RunCodeOnAllXLSFiles()
    Dim wbSrc As Workbook
    Dim wbCodeBook As Workbook
    Dim MyPath As String
    Dim strFilename As String
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set wbCodeBook = ThisWorkbook
     
        MyPath = "C:\Users\Maneet\Desktop\Baseline"
        strFilename = Dir(MyPath & "\*.csv", vbNormal)
         
        If Len(strFilename) = 0 Then Exit Sub
        Do Until strFilename = ""
                 'Open Workbook x and Set a Workbook variable to it
               wbSrc = Workbooks.Open(filename:=MyPath & "\" & strFilename)
                        Range("A21").Select
                        Range(Selection, Selection.End(xlToRight)).Select
                        Range(Selection, Selection.End(xlDown)).Select
                        Selection.Copy
                        Windows("processing_template.xlsx").Activate
                        Range("A2").Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                        Application.CutCopyMode = False
                        ChDir "C:\Users\Maneet\Desktop\Baseline_Processed"
                        ActiveWorkbook.SaveAs filename:= _
                            "C:\Users\Maneet\Desktop\Baseline_Processed\" & wbSrc.Name & " " _
                            , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                                      wbSrc.Close SaveChanges:=True
                 
                strFilename = Dir()
            Loop

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Any help/feedback would be greatly appreciated :rolleyes:
 

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