copy data from 40 sheets to one consolidated sheet in different columns

[N Prakash]

Hi,

I have 40 different sheets of data and data is in column A only in each sheet. I need to create a consolidated sheet by copying data from each sheet (Column A only) to a different sheet in 40 different columns. I was looking for Macro code and I was unable to find one. Can anybody help me please.

Thanks in advance,
Prakash

 

[JackDanIce]

What are the name of the sheets you are copying from and what is the name of the sheet you are copying to?

What cell should the data first copy to - A1?

 

[N Prakash]

I have 40 sheets with following names r1c2 r1l1 r1l2 r1r1 r1r2 r2c1 r2c2 r2l1 r2l2 r2r1 r2r2 r3c1 r3c2 r3l1 r3l2 r3r1 r3r2 r4c1 r4c2 r4l1 r4l2 r4r1 r4r2 r5c1 r5c2 r5l1 r5l2 r5r1 r5r2 r6c1 r6c2 r6l1 r6l2 r6r1 r6r2 pi1 pi2 pi3 pi4 r1c1. Data is there only in column A with sheet name as header of each column A. Macro code should copy and paste from each sheet only data from column A and put it into the consolidated sheet in 40 different columns. Hope I have made it clear.

 

[JackDanIce]

What is the name of the sheet you are copying the data to?

 

[N Prakash]

I am creating a new sheet "consolidated" or macro code itself can create a new sheet and copy from sheets r1c2, r1l1, r1l2, r1r1, r1r2, r2c1 etc., etc.,

 

[Nabeel Gondal]

you could use RDBmerger for this, see below link..!
https://www.rondebruin.nl/win/addins/rdbmerge.htm

 

[N Prakash]

This is returning error for me. Without Browse by just entering other options it is prompting to browse. If I open new workbook and try to browse, the file is not being displayed in the folder. Am I doing anything wrong.

 

[N Prakash]

It did not work. It has created a log file with error and file names are in a column. What I need is I have 40 sheets with data in column A and each data is more than 1000 rows. I want code should loop through all sheets and copy A column from each sheet in a new single sheet in different 40 columns from 40 individual sheets.

 

[Fluff]

Does this do what you want?

Sub CopyColA()

    Dim Cnt As Long
    Dim Sht As Worksheet
    Dim Shts As Long
    
    Shts = Sheets.Count
    Sheets.Add after:=Sheets(Shts)
    Set Sht = Sheets(Shts + 1)
    
    For Cnt = 1 To Shts
        Sheets(Cnt).Columns(1).Copy Sht.Columns(Cnt)
    Next Cnt

End Sub

 

[N Prakash]

Thanks a ton Mr. Fluff. I was struggling since 2 days. It met my requirements. Thanks all who responded to the thread.