Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Repeating my code until the last row of data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Repeating my code until the last row of data

    Hi ,

    I have the data set

    COL A COL B COL C COL D COL E
    ROW1 DATA DATA DATA
    ROW2 DATA DATA DATA
    ROW3 DATA DATA DATA
    ROW4 DATA DATA DATA
    ROW5 DATA DATA DATA
    ROW6 DATA DATA DATA
    ROW7 DATA DATA DATA


    At the moment my code copies the information from row 1. However, I need this to happen for the last row of a spreadsheet. As the last row differs from each spreadsheet.

    Can someone help amend my code below so it is able to function for data until the last row ?



    Code:
    Sub data()
    
    
    'copying projects
    ActiveSheet.Range("A3:L3").Copy
    Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, -13).PasteSpecial Paste:=xlPasteValues
    
    
    With Sheets("sheet1")
        Set rng = .Range(.Cells(1, 15), .Cells(1, .Cells(1, .Columns.Count).End(xlToLeft).Column))
        Set rng1 = .Range(.Cells(2, 15), .Cells(2, .Cells(2, .Columns.Count).End(xlToLeft).Column))
        Set rng_3 = .Range(.Cells(3, 13), .Cells(3, .Cells(3, .Columns.Count).End(xlToLeft).Column))
    End With
    
    
    'copying roles and planned hours
    
    
    rng.Copy
    Sheets("Sheet4").Range("N1").End(xlDown).Offset(0, -1).PasteSpecial xlValues, Transpose:=True
    rng1.Copy
    Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, 0).PasteSpecial xlValues, Transpose:=True
    
    
    'copy_actual hours
    rng_3.Copy
    Sheets("Sheet4").Range("N1").End(xlDown).Offset(-51, 1).PasteSpecial xlValues, Transpose:=True
    
    
    
    
    End Sub
    Thanks in advanced.




  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    Quote Originally Posted by KNKN9 View Post
    Hi ,


    At the moment my code copies the information from row 1. However, I need this to happen for the last row of a spreadsheet. As the last row differs from each spreadsheet.

    Can someone help amend my code below so it is able to function for data until the last row ?


    Are you asking to have this code run against the last row of your sheet or are you asking for this code to run on every line of your sheet, all the way down to the last row?
    Last edited by Steve_; Aug 5th, 2019 at 12:36 PM.

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    Thank you for your response and i want the code to run on every line of my sheet, all the way down to the last row

    Quote Originally Posted by Steve_ View Post
    Are you asking to have this code run against the last row of your sheet or are you asking for this code to run on every line of your sheet, all the way down to the last row?

  4. #4
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    If someone can help with this. That'll be much appreciated.

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,727
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Repeating my code until the last row of data

    Which column can we look at that will always have data so that we can use that column to determine where the last row of data is?
    Will column A always be populated, or will it be some other column?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    column A

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,727
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Repeating my code until the last row of data

    So then you can find the last row on a sheet like this:
    Code:
    Dim lr as Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    So then if you want to copy columns A:L down to last row, you could make the following change to your code:
    Code:
    ActiveSheet.Range("A3:L" & lr).Copy
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    Thanks...

    However, I need A3 to change also ( so drop down each row).. What I am trying to do is change my table above from

    COL A COL B COLC COL D COL E
    ROW 1 Role 1 Role 1 Role 1 Role 1 Role 1
    ROW 2 String 1 String 2 String 3 String 4 String 5
    ROW 3 project 1 values values values values values
    ROW 4 project 2 values values values values values
    ROW 5 project 3 values values values values values
    ROW 6 project 4 values values values values values


    to

    COL A COL B COLC COL D COL E
    ROW 1 project 1 Role 1 String 1 values
    ROW 2 Role 1 String 2 values
    ROW 3 Role 1 String 3 values
    ROW 4 Role 1 String 4 values
    ROW 5 Role 1 String 5 values
    ROW 6 project 2 Role 1 String 1 values
    Role 1 String 2 values
    Role 1 String 3 values
    Role 1 String 4 values
    Role 1 String 5 values



    Quote Originally Posted by Joe4 View Post
    So then you can find the last row on a sheet like this:
    Code:
    Dim lr as Long
    lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    So then if you want to copy columns A:L down to last row, you could make the following change to your code:
    Code:
    ActiveSheet.Range("A3:L" & lr).Copy

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,727
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Repeating my code until the last row of data

    Your code would cause errors, even on the first record, unless there is already something on Sheet4 to start. Otherwise, this row would bomb out, as it goes to the bottom of the page, and then tries to move one row past that:
    Code:
    Sheets("Sheet4").Range("N1").End(xlDown).Offset(1, -13).PasteSpecial Paste:=xlPasteValues
    So, what does Sheet4 look like to start, before this macro runs?
    Last edited by Joe4; Aug 6th, 2019 at 12:21 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Mar 2017
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Repeating my code until the last row of data

    Yes therebis data is sheet4
    So
    COL COL M COL N
    Header 1 header 2 header 3 .....
    Value value value
    Blank blank value

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
  •