LastRow Code Debug + Copy Formula in last row
Results 1 to 5 of 5

Thread: LastRow Code Debug + Copy Formula in last row

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default LastRow Code Debug + Copy Formula in last row

    Hello, wondering if someone can help me.

    I have put together the below code but not sure why it isn't working. I am trying to copy my formulas in range B8:F8, to all the way down to the last row - this last row number is calculated from the data in another sheet.

    So e.g. in the School Payment Request sheet there are 81 rows from the start of the data "C12". So I would want the formula in the sheet to copy to range B89:F89 etc.

    Just a bit stumped, any help appreciated thanks!

    Sub CommandButton1_Click()


    Dim mylastRow As Long
    mylastRow = Worksheets("School Payment Request").Range("C12" & Rows.Count).End(xlDown).Row
    Range("B8:F8").AutoFill Destination:=Range("B9:F" & mylastRow), Type:=xlFillDefault
    MsgBox "Imported"


    End Sub

  2. #2
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,400
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)

    Default Re: LastRow Code Debug + Copy Formula in last row

    This isn't going to work:

    Code:
    mylastRow = Worksheets("School Payment Request").Range("C12" & Rows.Count).End(xlDown).Row
    For example, if Rows.Count = 1048576 (as it is in my version of Excel) you have:

    Code:
    mylastRow = Worksheets("School Payment Request").Range("C121048576").End(xlDown).Row
    You may well get an error on the above. Using your method, you should just remove the "& Rows.Count" piece to leave:

    Code:
    mylastRow = Worksheets("School Payment Request").Range("C12").End(xlDown).Row
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  3. #3
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LastRow Code Debug + Copy Formula in last row

    Thanks for getting back to me, WBD, have amended as suggested. However I still get a debug error stating that "Autofill method of Range class failed".

  4. #4
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,400
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)

    Default Re: LastRow Code Debug + Copy Formula in last row

    Ah. You also need to change the target range to include the original selection I think:

    Code:
    Range("B8:F8").AutoFill Destination:=Range("B8:F" & mylastRow), Type:=xlFillDefault
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  5. #5
    New Member
    Join Date
    Jan 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: LastRow Code Debug + Copy Formula in last row

    doh! Thank you, literally been driving me crazy for last hour and a half!

    Quote Originally Posted by wideboydixon View Post
    Ah. You also need to change the target range to include the original selection I think:

    Code:
    Range("B8:F8").AutoFill Destination:=Range("B8:F" & mylastRow), Type:=xlFillDefault
    WBD

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
  •