Consolidating 4 seperate worksheets

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
please elaborate? do you mean you want to include the sheet name where the data came from?
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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;
Code:
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,652
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,536
Members
410,547
Latest member
htran4
Top