Combining cells from multiple worksheets into rows on single worksheet

aktheexcelguy

New Member
Joined
Sep 19, 2019
Messages
3
Hi, I am hoping someone is able to help me. I have a workbook with about 300 tabs. All exactly the same format. I am trying to write a macro that copies all of the data from several cells in each workbook into a single workbook. Let me give more an example.

A1 - First Name
B1 - Last Name
C1 - Phone Number

Result:

Worksheet - Collated
A1 - FIRST NAME
B1 - FIRST NAME
C1 - PHONE NUMBER
NEXT ROW WILL PULL FROM NEXT SHEET
A2 - FIRST NAME
B2 - LAST NAME
C2 - PHONE NUMBER
NEXT ROW WILL PULL FROM THE NEXT SHEET
A3 - FIRST NAME

and so on.

Please someone help explain to me how to do this. I am really stuck.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,405
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about

Code:
Sub MM1()
Dim r As Long, Ur As Range, I As Integer
For I = 2 To Sheets.Count
    Set Ur = Sheets("Master").UsedRange
    If I > 2 Then
     On Error Resume Next
        Set Ur = Sheets("Master").Cells(Ur.Rows.Count + 1, 1)
    End If
    Sheets(I).Activate
    ActiveSheet.UsedRange.Copy Ur
Next I
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
I'm assuming this is a mistake.
You said:
I am trying to write a macro that copies all of the data from several cells in each
workbook into a single
workbook

I assume you mean Worksheet.

Am I correct?

 
Last edited:

aktheexcelguy

New Member
Joined
Sep 19, 2019
Messages
3
I'm assuming this is a mistake.
You said:
I am trying to write a macro that copies all of the data from several cells in each
workbook into a single
workbook

I assume you mean Worksheet.

Am I correct?


Yes you are correct. I meant to say workbook with a ton of worksheets. Problem is that the cells I need to copy are all over the shop. A1, B23, B12......
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,405
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

In that case you will need to provide ALL the necessary information !!
The code I provided will copy all of the data from each sheet onto the Master Sheet
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,744
I have a workbook with about 300 tabs. All exactly the same format.

Having a workbook like that is rarely the way to go (just in my humble opinion of course).
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

In your original post you said

A1 - First Name
B1 - Last Name
C1 - Phone Number


I thought you meant copy these three ranges from each sheet into sheet named Collated.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,405
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Having a workbook like that is rarely the way to go
I'm guessing a sheet for each customer / client / patient, with a need for their personal data on a Master...??
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,653
Office Version
  1. 2013
Platform
  1. Windows
That is what I thought till he said:
I need to copy are all over the shop. A1, B23, B12......




I'm guessing a sheet for each customer / client / patient, with a need for their personal data on a Master...??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,405
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I'll put this up and the OP can adjust the range to suit.
But I'm willing to bet there is more to this than we see here, it'll get MUCH bigger...:LOL:

Code:
Sub MM1()
Dim ws As Worksheet, c As Range, dest As Range, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row
For Each ws In Worksheets
    If ws.Name <> "Master" Then
    Set dest = Sheets("Master").Range("A" & lr)
        For Each c In ws.Range("A1, B23, C12")
                dest.Value = c.Value
                Set dest = dest.Offset(0, 1)
        Next c
    End If
lr = lr + 1
Next ws
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,656
Messages
5,660,145
Members
418,553
Latest member
judithcatherine

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top