consolidate columns data from multiple sheets

[sudharshan]

i have 200 worksheets in my workbook . there are some unique header( columns) in all work sheets but these columns are not at same location (i.e take one header in sheet 1 at columnG and same header at sheet 2 is in column"M"), i would like to consoldiate first three headers data and last 9 headers data from all sheets .please help me

 

[Smallman]

Hi Sudharshan

Welcome to the forum. When you say;

i would like to consoldiate first three headers data and last 9 headers data from all sheets

What are the names of the first three headers (they are in different places so the names are gold). What is the layout of the last 9 headers. Are they just the last 9 columns. Maybe you could upload a sample 2-3 sheets to give a visual representation of your problem.

Take care

Smallman

 

[sudharshan]

ok

 

[Smallman]

So you are after;

Date; Company Name; Ticker

And the last 9 columns from the right in Row 2?

Take care

Smallman

 

[Smallman]

Data integrity is almost always a problem when you have so many sheets.

Option Explicit
Sub Testo()
Dim ws As Worksheet
Dim ar As Variant
Dim j As Integer
Dim lc As Long
Dim i As Integer
 
ar = [{"Date", "Company Name", "Ticker"}]
Sheet1.Cells(Rows.Count, 1).End(xlUp)(2).Copy
 
    For Each ws In Sheets
    If ws.Name <> "Consolidate" Then
        For i = 1 To 3
            j = ws.Rows("1:2").Find(ar(i)).Column
            ws.Range(ws.Cells(3, j), ws.Cells(ws.Cells(ws.Rows.Count, j).End(xlUp).Row, j)).Copy _
            Sheet1.Cells(Rows.Count, i).End(xlUp)(2)
        Next i
    lc = ws.Range("IV2").End(xlToLeft).Column
    ws.Range(ws.Cells(3, lc), ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row, lc - 8)).Copy _
    Sheet1.Cells(Rows.Count, 4).End(xlUp)(2)
    End If
    Next ws
End Sub

How are you going to ensure that Col 4 of the Data Item Column is always filled at the bottom. This will pose problems with the method I am suggesting. I would propose if there are 200 items that the data gets transferred through an array as it will grind a bit with so much copying and pasting. This method is good for a smaller amount of data.

Take care

Smallman

 

[sudharshan]

thanks alot sir.

 

[Smallman]

Sudharshan

Thanks and good luck with your project as I can see some holes which I went into a little above.

Take care

Smallman