save each Sheet to UNIQUE Sheet
Results 1 to 6 of 6

Thread: save each Sheet to UNIQUE Sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default save each Sheet to UNIQUE Sheet

    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

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,756
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: save each Sheet to UNIQUE Sheet

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Feb 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: save each Sheet to UNIQUE Sheet

    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

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,756
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: save each Sheet to UNIQUE Sheet

    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 by My Aswer Is This; Feb 8th, 2019 at 08:51 PM.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Feb 2006
    Posts
    197
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: save each Sheet to UNIQUE Sheet

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

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,756
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: save each Sheet to UNIQUE Sheet

    Glad I was able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by jamiguel77 View Post
    Worked Perfectly.....
    wwwwwwwooooooooooooooooooooooooooooowwwwwwwwwww
    Much thanks....
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •