begin_09
09-16-2019, 03:02 PM
Hi, I'm trying to automate the process of pasting columns from one workbook to another workbook, but the added complexity is that I want excel to partially match up the name of the workbook I'm copying from to the worksheet I'm copying to to ensure that the information is going to the right place. I also want to add Excel to loop through all the workbooks in my folder for all the sheet names I have, so I'm looking for something flexible enough to recognize partial name matches. I googled a lot and can't fit the code together the way I want it to, so would appreciate your help.
The folder I want the code to look through has workbooks whose names are formatted like this:
BH18PRME_BatchCFS_Abcd_201907.csv
AMAR19B6_BatchCFSF_Abcd_201907.csv
When you open up for instance, the first .csv file, it only has one sheet labeled with the same name as the workbook. I want to copy all the info in columns A:P to columns AF:AU on the corresponding AH18PRME sheet in my Master workbook.
My Master workbook has the following tabs:
1. Name Mapping: List of the names I want excel to partially find in the workbook name and then the corresponding sheet name. In the code below, I tried naming the range F4:F5 "Name" so I could refer to it more easily.
2. BH18PRME: I want columns AF:AU on this sheet filled in with the data from the BH18PRME workbook
3. AMAR19B6: I want columns AF:AU on this sheet filled in with the data from the AMAR19B6 workbook
4. AMAR19B6 Complete: Shows columns AF:AU filled in with the data from the AMAR19B6_BatchCFSF_Abcd_201907.csv file
In my code, I refer to two workbooks, one is my Master workbook, and one I call Trepp which is the workbook that is currently open that I need to copy data from. I tried using a counter to go through the list of names I want to match up between my workbook name and worksheet name, but wasn't sure how to code it correctly. I'm also not sure how to add the right If statements in here to say if my excel worksheet name is a partial match to the workbook name, then paste the data in the worksheet.
Below is my code thus far:
Sub open_csv_file2()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim lasersn As String
Dim wb As String
Dim trepp As Workbook
Dim Masterwb As Workbook
Dim counter As Double
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
Set Masterwb = Workbooks("Master Workbook.xlsm")
counter = 1 To Selection.Count
lasersn = Masterwb.Worksheets("Name Mapping").Range("F4").Offset(counter - 1, 0)
sPath = "E:\Cash Flow"
sFil = Dir(sPath & lasersn & "*.csv")
Do While sFil <> ""
strName = sPath & sFil
Workbooks.Open (strName)
Set trepp = Workbooks(sFil)
trepp.Activate
Masterwb.Worksheets("Name Mapping").Range("AF:AU").Value = trepp.Worksheets(1).Range("A:P").Value
Next counter
The folder I want the code to look through has workbooks whose names are formatted like this:
BH18PRME_BatchCFS_Abcd_201907.csv
AMAR19B6_BatchCFSF_Abcd_201907.csv
When you open up for instance, the first .csv file, it only has one sheet labeled with the same name as the workbook. I want to copy all the info in columns A:P to columns AF:AU on the corresponding AH18PRME sheet in my Master workbook.
My Master workbook has the following tabs:
1. Name Mapping: List of the names I want excel to partially find in the workbook name and then the corresponding sheet name. In the code below, I tried naming the range F4:F5 "Name" so I could refer to it more easily.
2. BH18PRME: I want columns AF:AU on this sheet filled in with the data from the BH18PRME workbook
3. AMAR19B6: I want columns AF:AU on this sheet filled in with the data from the AMAR19B6 workbook
4. AMAR19B6 Complete: Shows columns AF:AU filled in with the data from the AMAR19B6_BatchCFSF_Abcd_201907.csv file
In my code, I refer to two workbooks, one is my Master workbook, and one I call Trepp which is the workbook that is currently open that I need to copy data from. I tried using a counter to go through the list of names I want to match up between my workbook name and worksheet name, but wasn't sure how to code it correctly. I'm also not sure how to add the right If statements in here to say if my excel worksheet name is a partial match to the workbook name, then paste the data in the worksheet.
Below is my code thus far:
Sub open_csv_file2()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim lasersn As String
Dim wb As String
Dim trepp As Workbook
Dim Masterwb As Workbook
Dim counter As Double
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
Set Masterwb = Workbooks("Master Workbook.xlsm")
counter = 1 To Selection.Count
lasersn = Masterwb.Worksheets("Name Mapping").Range("F4").Offset(counter - 1, 0)
sPath = "E:\Cash Flow"
sFil = Dir(sPath & lasersn & "*.csv")
Do While sFil <> ""
strName = sPath & sFil
Workbooks.Open (strName)
Set trepp = Workbooks(sFil)
trepp.Activate
Masterwb.Worksheets("Name Mapping").Range("AF:AU").Value = trepp.Worksheets(1).Range("A:P").Value
Next counter