consolidation from multiple sheets

varunmol

New Member
Joined
Sep 6, 2016
Messages
21
Hi Everyone,

I am new to excel and don't know much about it.

I have a very simple problem
  • I have email addresses of customers in different sheets under one workbook
  • All the data is in the first row of each sheet (Row A) (Heading is - Email)
  • I just want to consolidate all the data into one sheet.
I hope I have explained the problem well. Any help is appreciated

Thank you so much!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,592
Office Version
  1. 2013
Platform
  1. Windows
You said:
  • All the data is in the first row of each sheet (Row A) (Heading is - Email
There is no Row A

Are you saying you want the entire row 1 of each sheet copied to a new sheet named "Alpha"

If not please explain more.
Or are you saying copy all column A from each sheet into a new sheet named "Alpha"
 

varunmol

New Member
Joined
Sep 6, 2016
Messages
21
well all the data is in Row 1 of each sheet
and i just want it all in a new sheet.

no matter what the name is... hope this is clear
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,592
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
Sub Copy_Row_One_To_Sheet_Alpha()
'Modified  3/11/2021  4:45:39 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets.Add(Before:=Sheets(1)).Name = "Alpha"
Lastrow = Sheets("Alpha").Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To Sheets.Count
        With Sheets(i)
            .Rows(1).Copy Sheets("Alpha").Cells(Lastrow, 1)
            Lastrow = Sheets("Alpha").Cells(Rows.Count, "A").End(xlUp).Row + 1
        End With
    Next
Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,163
Messages
5,640,519
Members
417,150
Latest member
cdguinn

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