Excel Macro to consolidate data from different worksheet

Hi there,

There are a lot of things that can be streamlined in this code.  For example, look at this portion of the code...

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "S" Then sLoc = ws.Index
        If ws.Name = "E" Then eLoc = ws.Index
    Next ws

Select allOpen in new window

You're looping through all of the worksheets in the workbook.  If you're only looking for the index value of those two worksheets, you only need two lines of code, no loop...

sLoc = ThisWorkbook.Worksheets("S").Index
eLoc = ThisWorkbook.Worksheets("E").Index

Select allOpen in new window

There are some portions of code you don't need.  There's no need to select anything.  Those can be taken out completely.  Plus they slow down code execution.  Take a look at this, it's streamlined somewhat...

Sub ConsolidateData()

    Dim ws                      As Worksheet
    Dim rWs                     As Worksheet

    Call TOGGLEEVENTS(False)

    'determine location of 'S' and 'E' tabs
    sLoc = ThisWorkbook.Worksheets("S").Index
    eLoc = ThisWorkbook.Worksheets("E").Index

    'Name of result sheet
    Set rWs = ThisWorkbook.Sheets("Example-SDW")

    'Clear Example-SDW tab
    lastRow = rWs.Range("M" & rWs.Rows.Count).End(xlUp).Row
    rWs.Range("M2:Y" & lastRow).ClearContents

    'now, loop through all sheets and copy data in Example-SDW tab
    If sLoc + 1 < eLoc Then

        For i = sLoc + 1 To eLoc - 1

            Set ws = ThisWorkbook.Worksheets(i)
            If ws.Name <> rWs.Name Then
                mySheet = ws.Name
                lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

                If lastRow > 1 Then
                    ws.Range("A2:L" & lastRow).Copy
                    pasteRow = rWs.Range("N" & rWs.Rows.Count).End(xlUp).Row + 1
                    rWs.Range("N" & pasteRow).PasteSpecial xlPasteAll
                    rWs.Range("M" & pasteRow & ":M" & pasteRow + lastRow - 2).Value = mySheet

                End If
            End If

        Next i

    End If

    Call TOGGLEEVENTS(True)

End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub

Select allOpen in new window

HTH

Regards,
Zack Barresse

Thanks for streamlining the code.  Can you also help me with the additional functionality? (see below from the original question https://www.experts-exchange.com/questions/27757450/Excel-Macro-to-consolidate-data-from-different-worksheet.html).

(1) flexibility to add or delete columns in the result sheet (the blue area).  In other words, not hard code the columns to paste data.  In this case, perhaps use the column heading "Name" as the location to perform clear cell content and paste data?

(2) The data tabs between "S" and "E" have additional rows that I don't need.  They contain the word "total" in the "Start Date" column.  Can we delete them after copy and paste to the result tab?

(3) Can the formulas in the blue area be automatically copied down to last row?

We can do all of that.  We just need the logic.

(1) We would need to know any constraints.  You're copying all data in the target sheets (between S and E sheets), dynamic row, but the column is hard-coded (L).  Is this going to grow?  Will it always be the same?  If it grows or shrinks, will 1) all of the target sheets be the same, and 2) do you want to adjust the destination sheet initial columns (A:L) to accommodate a varying range of input data?

(2) A couple of things.  First, I don't see any rows that have a 'total' in it.  If there were, we could use code to just not grab that row.  So yes, we can do this.  We'd just need to know where to look.

(3) I don't know what formulas you're talking about.  ??

Let me go back a little further and ask a more fundamental question.  Why do you have the S and E sheets anyway?  Are they literally placeholders?  That looks like all they are doing.  We could structure the code to logically look at certain sheets if you'd like.  If you like that, visually, we can leave it in there, I just don't think it's needed.

Can you explain the data here?  Will this data grow?  Where does it come from?  Can it not be entered into one sheet from the start?  Some explanation would help a little.

Zack

Hi Zack, now I am thinking the easier way is to consolidate all data from all the different tabs into a blank worksheet (perhaps call it "data") instead of a worksheet that has calculations.

Here is the background:  I download monthly sales reports from a website and consolidate the data and apply calculations.  Each country/region has one report.  Currently there are about 20 reports and the number may grow in the future.  The reports have the same format.  The format may change in the future or the number of columns may increase but it should be the same for all countries in a given month.

So I'll put all the downloaded reports between the "S" and "E" tab and would like a script to do the following:

(1) consolidate all the data into one tab ("data" tab) starting column B
(2) add the tab name in column A.  The tab name will represent the name of the country/region
(3) remove lines in column B that begin with the word "Total"

Once I have the consolidated and cleaned up data in the "data" tab I can then transfer it to the calculation tab.  I think this is a better solution then consolidate the data directly into the calculation tab.  Does it make sense?

So when you download these reports, do they download as their own files?  If so, can you save them all to a specific folder?  If so, we can run code on all files in a folder and not have to worry about importing them into a workbook.

Better yet, perhaps you can tell us where you download it from and we can do it all via code?  Depending on how you're getting the data, we can automate it.

Thank you for the explanation, it helps.  :)

Zack

Hi Zack,  This is a great idea!  I typically download the files one by one from a website.  Unfortunately there is no download option to download all transactions in one single report.  However, it would be great if I can consolidate all downloaded files in a specific folder.  Also, I download the reports in a zip file format and I had to unzip them as .txt files.

Can you help me with the code?  Just put any path for the folder for now and I will change it to the specific one I use.

Thanks!