Link to home
Start Free TrialLog in
Avatar of spudmcc
spudmccFlag for United States of America

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 "PartnerApprovedDailyPostedSales".  (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
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Hey there...for the first part, I have written a short macro that will copy your BEFORE worksheet, rename it to NEW, detect the date in A3 and then fill in any line that has data, in its respective cell in column I.
Sub AddDateRow()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim mystr As String
Dim mydate As Date
Dim mycell As Range
Dim c As Range

orinsheet = "Before"    'Change the name to the origin sheet
destsheet = "New"       ' Change the name to destination sheet
Set ws = Application.ActiveWorkbook.Sheets(orinsheet)
mystr = ws.Range("A3").Value
mystr = Right(mystr, Len(mystr) - 10)
mydate = mystr
ws.Activate
Application.ActiveSheet.Copy After:=Worksheets(orinsheet)
ActiveSheet.Select
ActiveSheet.Name = destsheet
Set ws2 = ActiveSheet
'Insert date at end
For i = 6 To ws2.UsedRange.Rows.Count
    lc = "I" & i
    Set mycell = ws2.Range(lc)
    mycell.Select
    mycell.Value = mydate
Next
End Sub

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?
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 PartnerApprovedDailyPostedSales
3.  You want to create a NEW file where you want to copy the contents of all PartnerApprovedDailyPostedSales worksheets into a single worksheet.

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?
Avatar of spudmcc

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
Avatar of spudmcc

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
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

Open in new window

with the actual work being done by the Combine2 Macro.
ASKER CERTIFIED SOLUTION
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of spudmcc

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