spudmcc
asked on
Macro needed
Hi Experts!
I am in need of some assistance with this challenge. We receive on a daily basis many Excel files that come in in the same format. What we want to do is the following:
1--Take each of the files and add the "posted" date on the end of each row. I've attached an example so you can see exactly what we are looking for. The date is always located in A3. But it is "just a date" there is some text with it.
2--We want to take all the files in the daily folder "combine" and have the data in the sheet of each workbook labeled "PartnerApprovedDailyPoste dSales". (this is always the name) combine into 1 new workbook on a single tab stacked on top of one another. Just as if you cut and pasted each of the workbooks individually into one worksheet. The data always starts in A1 on row 6 but in varies in how many rows each workbook will have.
Any help would be very much appreciated.
Spudmcc
EXAMPLE.XLS
I am in need of some assistance with this challenge. We receive on a daily basis many Excel files that come in in the same format. What we want to do is the following:
1--Take each of the files and add the "posted" date on the end of each row. I've attached an example so you can see exactly what we are looking for. The date is always located in A3. But it is "just a date" there is some text with it.
2--We want to take all the files in the daily folder "combine" and have the data in the sheet of each workbook labeled "PartnerApprovedDailyPoste
Any help would be very much appreciated.
Spudmcc
EXAMPLE.XLS
For the second part, do you mean that
1. You have all similar Excel files in a folder, ie C:\MySales
2. All Excel files have a worksheet named PartnerApprovedDailyPosted Sales
3. You want to create a NEW file where you want to copy the contents of all PartnerApprovedDailyPosted Sales worksheets into a single worksheet.
Correct?
1. You have all similar Excel files in a folder, ie C:\MySales
2. All Excel files have a worksheet named PartnerApprovedDailyPosted
3. You want to create a NEW file where you want to copy the contents of all PartnerApprovedDailyPosted
Correct?
Can I assume that all data begins from ROW 6? and Row 6 and that the header in Row 5 is needed only once?
ASKER
We will have maybe 10 of these workbooks in a single folder "combine". What we want to do is create a new workbook and merge all of the other workbooks in the folder taking the data from each (from row 6 down) and put it in 1 single worksheet but stack the data. In other words, take workbook 1 data and put it on the top of the sheet. Take workbook 2 data and put it under the workbook 2 data. Do this for all the workbooks in the folder and combine into the new workbook with the single sheet.
The objective is to have one worksheet with all the data from all the workbooks in one book.
Hope that helps.
A
The objective is to have one worksheet with all the data from all the workbooks in one book.
Hope that helps.
A
ASKER
Also, could we possible take your solution from above and configure it so that it would work after all of the workbooks are combined. In other words instead of assigning it to just one worksheet name at a time have it do your script for all worksheets.
A
A
Hey there..given that the dates will be different, it is probably better to first run the 1st macro for the date first for all excel files and then combine
Now in order to combine the worksheets, assuming they are all in a specific path (ie c:\combine) and all sheets you want to combine have the same name (ie New) then something like this would work:
Sub Combine1()
Dim wb As Workbook, ws As Worksheet
Dim fso As Object, f As Object
Dim FilePath As String
'Get data folder.
FilePath = "C:\combine" 'you can replace with the actual path the excel files are in
Set fso = CreateObject("scripting.filesystemobject")
For Each f In fso.getfolder(FilePath).Files
If f.Type Like "*Excel*" Then
Set wb = Workbooks.Open(f.Path)
Call Combine2
wb.Close True
End If
Next f
End Sub
with the actual work being done by the Combine2 Macro.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much for your time, patience and talent. Your solution will save us many hours and bandwidth that we just don't have now.
Thanks again!
A
Thanks again!
A
Open in new window
Not sure what you mean by the second part. In addition, your Before and After worksheets had differences in data - for example in the Product Name and the TITLE of the worksheet...any rule there?