none
Autofill macro for specific worksheet not working in other worksheets RRS feed

  • Question

  • I am trying to get an autofill copy and paste macro to work so the user can press a button and run the macro from another worksheet in the same workbook.

    The macro will only work however when I am in the worksheet it refers to (Data). I'm unsure how to fix this, I've tried, way too long, in work so I have to take another approach.

    Sub CopyandPasteRows()
    Dim Data_sht As Worksheet
    Set Data_sht = ThisWorkbook.Worksheets("Data")
    Range("K2:Q2").AutoFill Destination:=Range("K2:Q" & Cells(Rows.Count, "J").End(xlUp).Row)
    End Sub

    I've also tried:

    Sub CopyandPasteRows()
    Worksheets("Data").Range("K2:Q2").AutoFill Destination:=Worksheets("Data").Range("K2:Q" & Cells(Rows.Count, "J").End(xlUp).Row)
    End Sub

    and this also only works when I am in the Worksheet Data. I want this to work when the user is in another worksheet.

    Any advice appreciated.

    Fiona


    Wednesday, June 10, 2015 5:00 PM

Answers

  • I figured it out for anyone looking for this as there is a lot out there on it but very little on specifying the Worksheet.

    Below is a macro that works from any Worksheet.

    Sub CopyandPasteRows()
    Dim lastrow As Long
    lastrow = Worksheets("Data").Range("J2").End(xlDown).Row
    With Worksheets("Data").Range("K2:Q2")
    .AutoFill Destination:=Worksheets("Data").Range("K2:Q" & lastrow&)
    End With
    End Sub
    

    • Marked as answer by FionaLego Wednesday, June 10, 2015 8:44 PM
    Wednesday, June 10, 2015 8:44 PM

All replies

  • I figured it out for anyone looking for this as there is a lot out there on it but very little on specifying the Worksheet.

    Below is a macro that works from any Worksheet.

    Sub CopyandPasteRows()
    Dim lastrow As Long
    lastrow = Worksheets("Data").Range("J2").End(xlDown).Row
    With Worksheets("Data").Range("K2:Q2")
    .AutoFill Destination:=Worksheets("Data").Range("K2:Q" & lastrow&)
    End With
    End Sub
    

    • Marked as answer by FionaLego Wednesday, June 10, 2015 8:44 PM
    Wednesday, June 10, 2015 8:44 PM
  • Hi FionaLego,

    Thanks for sharing your solution.

    Regards                      

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 11, 2015 1:22 AM
    Moderator