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 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.
Any help/feedback would be greatly appreciated
- 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
