none
How to copy same range from multiple worksheets and paste into a new worksheet.

    Question

  • my workbook contains more than 40 worksheets and i need copy the same range from each of

    these worksheet into a new worksheet in the same columns.

    can anybody help me solving this problem, thanks a million.

    Tuesday, April 26, 2011 3:16 AM

Answers

  • my workbook contains more than 40 worksheets and i need copy the same range from each of

    these worksheet into a new worksheet in the same columns.

    can anybody help me solving this problem, thanks a million.

    1. Create a new sheet and rename: Archive
    2. Copy/paste the maco below in a standard module(ALT+F11, Insert-->Module)
    3. ALT+F11
    4. ALT+F8
    5. Select: m()
    6. Run

    Public Sub m()
        Dim lRow As Long
        Dim sh As Worksheet
        Dim shArc As Worksheet
        Set shArc = ThisWorkbook.Worksheets("Archive")
        For Each sh In ThisWorkbook.Worksheets
            Select Case sh.Name
                Case Is <> "Archive"
                    lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                    sh.Range("A1:A100").Copy _
                        destination:=shArc.Range("A" & lRow)
            End Select
        Next
        Set shArc = Nothing
        Set sh = Nothing
    End Sub

    In this example is the Range("A1:A100") copied from several sheets other then Archive and pasted into column A of sheet Archive


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/

    Tuesday, April 26, 2011 12:11 PM
  • See also
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Mauro Gamberini" wrote in message news:9d373a37-843c-472a-8697-2466e0479f5d...

    my workbook contains more than 40 worksheets and i need copy the same range from each of

    these worksheet into a new worksheet in the same columns.

    can anybody help me solving this problem, thanks a million.

    1. Create a new sheet and rename: Archive
    2. Copy/paste the maco below in a standard module(ALT+F11, Insert-->Module)
    3. ALT+F11
    4. ALT+F8
    5. Select: m()
    6. Run

    Public Sub m()
        Dim lRow As Long
        Dim sh As Worksheet
        Dim shArc As Worksheet
        Set shArc = ThisWorkbook.Worksheets("Archive")
        For Each sh In ThisWorkbook.Worksheets
            Select Case sh.Name
                Case Is <> "Archive"
                    lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                    sh.Range("A1:A100").Copy _
                        destination:=shArc.Range("A" & lRow)
            End Select
        Next
        Set shArc = Nothing
        Set sh = Nothing
    End Sub

    In this example is the Range("A1:A100") copied from several sheets other then Archive and pasted into column A of sheet Archive


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/

    Tuesday, April 26, 2011 4:59 PM
  •  

              Thanks, you really helped.

    My range has three colums actually, and i just changed the Range as Range(A10:C60), the macro still worked, do i need some other

    changes to the marco to make it more robust?

    And if i want to use PasteSpecial, could you give me more tips?  Thanks.




    Try:

    Public Sub m()
         Dim lRow As Long
         Dim sh As Worksheet
         Dim shArc As Worksheet
         Set shArc = ThisWorkbook.Worksheets("Archive")
         For Each sh In ThisWorkbook.Worksheets
             Select Case sh.Name
                 Case Is <> "Archive"
                     lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row+1
                     sh.Range("A1:C60").Copy
                     shArc.Range("A" & lRow).PasteSpecial
             End Select
         Next
         Application.CutCopyMode = False
         Set shArc = Nothing
         Set sh = Nothing
     End Sub


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/
    • Marked as answer by alfred w Thursday, April 28, 2011 3:20 AM
    Wednesday, April 27, 2011 8:23 AM

All replies

  • my workbook contains more than 40 worksheets and i need copy the same range from each of

    these worksheet into a new worksheet in the same columns.

    can anybody help me solving this problem, thanks a million.

    1. Create a new sheet and rename: Archive
    2. Copy/paste the maco below in a standard module(ALT+F11, Insert-->Module)
    3. ALT+F11
    4. ALT+F8
    5. Select: m()
    6. Run

    Public Sub m()
        Dim lRow As Long
        Dim sh As Worksheet
        Dim shArc As Worksheet
        Set shArc = ThisWorkbook.Worksheets("Archive")
        For Each sh In ThisWorkbook.Worksheets
            Select Case sh.Name
                Case Is <> "Archive"
                    lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                    sh.Range("A1:A100").Copy _
                        destination:=shArc.Range("A" & lRow)
            End Select
        Next
        Set shArc = Nothing
        Set sh = Nothing
    End Sub

    In this example is the Range("A1:A100") copied from several sheets other then Archive and pasted into column A of sheet Archive


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/

    Tuesday, April 26, 2011 12:11 PM
  • See also
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm


    "Mauro Gamberini" wrote in message news:9d373a37-843c-472a-8697-2466e0479f5d...

    my workbook contains more than 40 worksheets and i need copy the same range from each of

    these worksheet into a new worksheet in the same columns.

    can anybody help me solving this problem, thanks a million.

    1. Create a new sheet and rename: Archive
    2. Copy/paste the maco below in a standard module(ALT+F11, Insert-->Module)
    3. ALT+F11
    4. ALT+F8
    5. Select: m()
    6. Run

    Public Sub m()
        Dim lRow As Long
        Dim sh As Worksheet
        Dim shArc As Worksheet
        Set shArc = ThisWorkbook.Worksheets("Archive")
        For Each sh In ThisWorkbook.Worksheets
            Select Case sh.Name
                Case Is <> "Archive"
                    lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                    sh.Range("A1:A100").Copy _
                        destination:=shArc.Range("A" & lRow)
            End Select
        Next
        Set shArc = Nothing
        Set sh = Nothing
    End Sub

    In this example is the Range("A1:A100") copied from several sheets other then Archive and pasted into column A of sheet Archive


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/

    Tuesday, April 26, 2011 4:59 PM
  •  

              Thanks, you really helped.

    My range has three colums actually, and i just changed the Range as Range(A10:C60), the macro still worked, do i need some other

    changes to the marco to make it more robust?

    And if i want to use PasteSpecial, could you give me more tips?  Thanks.

    1. Create a new sheet and rename: Archive
    2. Copy/paste the maco below in a standard module(ALT+F11, Insert-->Module)
    3. ALT+F11
    4. ALT+F8
    5. Select: m()
    6. Run

    Public Sub m()
        Dim lRow As Long
        Dim sh As Worksheet
        Dim shArc As Worksheet
        Set shArc = ThisWorkbook.Worksheets("Archive")
        For Each sh In ThisWorkbook.Worksheets
            Select Case sh.Name
                Case Is <> "Archive"
                    lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                    sh.Range("A1:A100").Copy _
                        destination:=shArc.Range("A" & lRow)
            End Select
        Next
        Set shArc = Nothing
        Set sh = Nothing
    End Sub

    In this example is the Range("A1:A100") copied from several sheets other then Archive and pasted into column A of sheet Archive


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/


    Wednesday, April 27, 2011 2:19 AM
  • Hi, thanks. I visit your website, your solution is really great and your website contains so many useful tips which help me a lot.
    Thanks again for your kind help.
    See also
     
     

    Regards Ron de Bruin
    http://www.rondebruin.nl/tips.htm

    Wednesday, April 27, 2011 2:43 AM
  •  

              Thanks, you really helped.

    My range has three colums actually, and i just changed the Range as Range(A10:C60), the macro still worked, do i need some other

    changes to the marco to make it more robust?

    And if i want to use PasteSpecial, could you give me more tips?  Thanks.




    Try:

    Public Sub m()
         Dim lRow As Long
         Dim sh As Worksheet
         Dim shArc As Worksheet
         Set shArc = ThisWorkbook.Worksheets("Archive")
         For Each sh In ThisWorkbook.Worksheets
             Select Case sh.Name
                 Case Is <> "Archive"
                     lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row+1
                     sh.Range("A1:C60").Copy
                     shArc.Range("A" & lRow).PasteSpecial
             End Select
         Next
         Application.CutCopyMode = False
         Set shArc = Nothing
         Set sh = Nothing
     End Sub


    Mauro Gamberini - Microsoft© MVP(Excel)
    http://www.maurogsc.eu/
    • Marked as answer by alfred w Thursday, April 28, 2011 3:20 AM
    Wednesday, April 27, 2011 8:23 AM
  • Hi,

    I am sherin. I was reading this forum and it was very useful!

    I have 100 multiple worksheets. I want to copy eg. B column (1 to 100 cells) from all worksheets and paste it in a single sheet, not in the single (same) column but different columns. 

    I tried your coding in microsoft excel and it works. Actually, your coding copies particular column of data from multiple worksheet and put together in the same column (eg. column A) of the sheet named as archive. But, i want to copy them not in the same column A, but in different columns in order to differentiate the columns from different multiple sheets. 

    How to modify the coding to copy particular column from multiple worksheet and put it in multiple columns of the same worksheet?


    Million Thanks!

    Sherin


    • Edited by sherin8 Sunday, July 17, 2016 12:47 PM
    Sunday, July 17, 2016 12:46 PM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Get selected column(s) from multiple worksheets and
    show them in the summary sheet in their own source identified columns.
    No formulas, no VBA macro needed.
    http://www.mediafire.com/download/bqa8z648oghtlyx/07_17_16.xlsx

    Share file to obviate learning PQ.

    Sunday, July 17, 2016 10:11 PM
  • Hi 

    I tried to copy columns from multiple datasheets in a summary sheet using New query option. But, i could not copy all columns in a summary sheet. 

    I had a look at the excel file you attached. I don't understand how did you copy the columns in the summary sheet. Could you please tell me the steps how to do it?...

    Many Thanks for your help!

    Sherin


    Tuesday, July 19, 2016 5:53 PM
  • It's not a matter of copying or adding steps,
    it's a matter of learning an alternate programming language to VBA, namely M.
    See this or other books:
    "M is for (Data) Monkey" by Ken Puls.

    Tuesday, July 19, 2016 7:02 PM