none
Macro to copy range from specific WS RRS feed

  • Question

  • Hi,
    I am trying to copy Range(A13:D16) from all Worksheets in the Active workbook except for the “Month1”,  “Month2”, and “Month3” Worksheets and paste the values in a new Workbook.

    Any help is greatly appreciated.

    Thanks

    Wednesday, May 17, 2017 7:59 PM

Answers

  • Hello,

    You could iterate through the sheets and use Range.Copy for pasting values.

    Here is the example

    Sub CopyRangeInAllSheets()
    Dim sht As Worksheet
    Dim wb As Workbook
    Dim newb As Workbook
    Dim newsht As Worksheet
    Set wb = ActiveWorkbook
    Set newb = Application.Workbooks.Add
    Set newsht = newb.Sheets(1)
    For Each sht In wb.Worksheets
    If sht.Name = "Month1" Then
    GoTo Continue    'if sht is month1, go to next loop
    End If
    LastRow = newsht.Cells(newsht.Rows.count, 1).End(xlUp).Row
    sht.Range("A13:D16").Copy newsht.Cells(LastRow + 1, 1)
    Continue:
    Next sht
    End Sub
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by D.Hanna Thursday, May 18, 2017 12:33 PM
    Thursday, May 18, 2017 2:16 AM
    Moderator

All replies

  • Hello,

    You could iterate through the sheets and use Range.Copy for pasting values.

    Here is the example

    Sub CopyRangeInAllSheets()
    Dim sht As Worksheet
    Dim wb As Workbook
    Dim newb As Workbook
    Dim newsht As Worksheet
    Set wb = ActiveWorkbook
    Set newb = Application.Workbooks.Add
    Set newsht = newb.Sheets(1)
    For Each sht In wb.Worksheets
    If sht.Name = "Month1" Then
    GoTo Continue    'if sht is month1, go to next loop
    End If
    LastRow = newsht.Cells(newsht.Rows.count, 1).End(xlUp).Row
    sht.Range("A13:D16").Copy newsht.Cells(LastRow + 1, 1)
    Continue:
    Next sht
    End Sub
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by D.Hanna Thursday, May 18, 2017 12:33 PM
    Thursday, May 18, 2017 2:16 AM
    Moderator
  • Thank you so much!
    Thursday, May 18, 2017 12:33 PM