save each Sheet to UNIQUE Sheet

[jamiguel77]

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

 

[My Aswer Is This]

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.

 

[jamiguel77]

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

 

[My Aswer Is This]

Try this:

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

 

[jamiguel77]

Worked Perfectly.....
wwwwwwwooooooooooooooooooooooooooooowwwwwwwwwww
Much thanks....

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Worked Perfectly.....
wwwwwwwooooooooooooooooooooooooooooowwwwwwwwwww
Much thanks....