Consolidating 4 seperate worksheets

[howard]

I have 4 worksheets where the structure is exactly the same except the figures differ

The worksheet have text and values in columns A to S. I need to write VBA code that will combine the data and show the descritions in a consolidated woorksheet.

Your assistance will be most appreciated


Howard

 

[agihcam]

please elaborate? do you mean you want to include the sheet name where the data came from?

 

[howard]

I want to include the sheet names from where the data came from

For eg if I have if I have 4 worksheets for eg Sheet1 + Sheet2 +Sheet3 +Sheet4 with the following columns and data, I would like to combine these into an new worksheet "sheet5"

Account No. Description Current Month Closing Bal


Your assistance will be most appreciated


Howard

 

[agihcam]

not sure about the exact layout of your spreadsheet. this code will put sheet name in columnT of summary sheet. copied data from individual sheet starting from row 2;

Sub consol()
Dim i, ii As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("summary").Delete
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "summary"
For i = 1 To 4
    For ii = 2 To Sheets(i).Range("a" & Rows.Count).End(xlUp).Row
        Sheets(i).Cells(ii, "t").Value = Sheets(i).Name
    Next
    
        Sheets(i).Range("a2:t1000").Copy
            With Sheets("summary").Range("a" & Rows.Count).End(xlUp).Offset(1)
                .PasteSpecial xlValues
            End With
    Sheets(i).Columns("t").ClearContents
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[howard]

Hi Agihcam

Thanks for your help.

It would be appreciated if you would provide me with your email address, so that I can attach my files if I need some further help

Howard

 

[agihcam]

the board is open for any questions you may have.

 

[howard]

Hi Agicam

A little while ago you provided me with code to consolidate 4 seperate worsheets (see code below)

I have just tested the code and the following comes up as a compile error

    For ii = 2 To Sheets(i).Range("a" & Rows.Count).End(xlUp).Row


Please test and correct

If I had 4 seperate workfiles and I wanted to combine these all into a new workfile named Consolidation Tax Schedules , how would the code look.

Look forward to hearing from you.

Howard

 

[agihcam]

is it 4 separate files or 4 separate sheets in a single workbook. My understanding from your original post is that, you have 4 worksheets.

 

[howard]

Hi Agihcam

At present I need to consolidate 4 seperate worksheets.

I also have 4 seperate workbooks where A need to consolidate all the data (there are several worksheets on each workbok) , that needs to be consolidated into a new workbook, to be named say "Consolidated Tax"

Your assistance will be most appreciated

Regards


Howard

 

[agihcam]

Hi Agihcam

At present I need to consolidate 4 seperate worksheets.

I haven't seen any error with mine.
I have Sheet1,Sheet2,Sheet3,Sheet4 with data in a single workbook. after running the code I've got a consolidated data in new sheet called summary.