No announcement yet.

Running Macro on multiple worksheets using prefix and copy data to other worksheet

  • Filter
  • Time
  • Show
Clear All
new posts

  • Running Macro on multiple worksheets using prefix and copy data to other worksheet

    I am working on a worksheet with multiple tabs although I need to copy data from worksheets that start with only "2018 and 2017" and then paste it to summary tab.

    At Top of These sheet are headers and other calculation which i don't need to copy.(refer Row# 1 to 10 in attached snap shot)
    From these sheets I wanted to copy range which is starting from row 13 but this row# can change sheet to sheet, so I have created a variable rng which can select column L and then select column A using XLtoLEFT

    I am not able to run this code using variable to select the range, if i select data range using cell reference example("A13:L26")its works fine but getting error while using variable to select the range. Also is there a way by which I can exclude the headers?

    I am getting error with range selection (object defined error)

    Below is my codes

     Sub NEWWORK()
    Dim sheet As Worksheet
    Dim rng As Range  
    For Each sheet In Worksheets
    If (Left(sheet.Name, 4) = "2018") Or (Left(sheet.Name, 4) = "2017") Then
    With sheet
    Set rng = .Range(.Range("L1").End(xlDown).Offset(1, 0), _  
      .Cells(Rows.Count, "L").End(xlUp)).select
    Range(Selection, Selection.End(xlToLeft)).Select
    End With 
    With Worksheets("Summary")
    .Cells(Rows.Count, 1).End(xlUp).Resize(rng.Rows.Count, 1).Value = rng.Value
    End With
     End If
    Next sheet
     End Sub

  • #2


    This question has been posted in at least one other forum.
    Do not post any further responses in this thread while this post remains.

    Whilst we would rather you didn't cross-post, we know that there may be a valid reason for doing so. All we ask is that you provide the links in your original post to your question in the other forum(s). This is also one of the rules that you agreed to upon joining.

    Ozgrid is full of volunteers that give up their own time to help others, something that should be respected and not taken for granted. Itís never nice for someone to find out that a problem they have spent time solving for you has already been answered somewhere else without them knowing, and so we ask you to make it clear that you have also asked elsewhere.

    Please update your original post to include the link(s).

    You also posted a previous question but didn't bother to acknowledge my response.