save each Sheet to UNIQUE Sheet

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
374
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
hi friends..

How to do:

i have 23 sheets with similar structure and format...
so so each sheet the minimum rows was 10 (and 20 cols)
and the maximum rows was 150 (and 20 columns).


my question is how to generate unique sheet... with all info of the 23 sheets.


thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
So at this time you have 23 sheets in your workbook.

And you want to create a new sheet name Master and copy all the rows from all those 23 sheets into sheet named master.

Is this correct?

Is there always data in Column A of each sheet.

So the last row in each sheet can be determined by look down column A of each sheet

So if sheet(12) column A has values down as far as row 20 we copy 20 rows.

Would that be correct.

And do we want to copy formatting formulas all or just copy values.

Please answer all questions please.

Are there any other details I need ??
Like do we copy row(1) of each sheet which normally has Headers.

Do we need to identify in some way what sheet this data came from?

See if you have extra details like this please let us know now.
 
Upvote 0
yes i want creat a master sheet.

Yes always have values.. in all rows and Columns... of each sheet, (of couse sheet 1 have 27 rows, sheet5 140 and so on, diferent number of rows).

(yes, you are correct in sample of Collumn A)

i want copy only Values.

yes i want copy all rows... not important... if have headers/titles...


only with this info or need more?

much thanks
 
Upvote 0
Try this:
Code:
Sub Copy_Sheets_To_Master()
'Modified  2/8/2019  8:45:28 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets.Add(Before:=Sheets(1)).Name = "Master"
    For i = 2 To Sheets.Count
        Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrow = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        Sheets(i).Rows(1).Resize(Lastrow).Copy
        Sheets("Master").Rows(Lastrowa).PasteSpecial xlPasteValues
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Worked Perfectly.....
wwwwwwwooooooooooooooooooooooooooooowwwwwwwwwww
Much thanks....
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top