Results 1 to 5 of 5

Thread: Copy and Paste down to last Row +

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,617
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Copy and Paste down to last Row +

    I have a table where I need to paste data into (column F)

    But I need to past in in the next empty row in F down to the last row where there is data in column A

    So if I have data in Column A down to row 25 And I have data in column F currently down to row 5, I want to past my new data from row 6 down to 25 (in column F)



    Help Please!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,449
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copy and Paste down to last Row +

    Code:
    Sub Test()
    Dim LrA As Long, LrF As Long
    LrA = Range("A" & Rows.Count).End(xlUp).Row
    LrF = Range("F" & Rows.Count).End(xlUp).Row
    Range("B2").Copy Destination:=Range(Cells(LrF + 1, "F"), Cells(LrA, "F"))
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,617
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste down to last Row +

    Thanks JoeMo

    I tried to modify your code to fit mine without success. (see last row of code

    [code]
    Dim lstrwI As Long
    Dim lstrwB As Long

    lstrwI = Sheets("CMOPUpload").Range("A" & Rows.Count).End(xlUp).Row
    lstrwB = Sheets("CMOPUpload").Range("F" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[1],""Yes"")"
    If ActiveSheet.Range("B5").Value > 0 Then

    ActiveSheet.ListObjects("ModelGeneral_vluBuyer__2").Range.AutoFilter Field:=3 _
    , Criteria1:="Yes"

    Range("A7").Select
    ActiveCell.Offset(1, 0).Select

    Range(Selection, Selection.End(xlDown).Offset(0, 1)).Copy
    Sheets("CMOPUpload").Range(Cells(lstrwB + 1, "F"), Cells(lstrwI, "F")).PasteSpecial xlPasteValues

    End If

    [code/]
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  4. #4
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,617
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy and Paste down to last Row +

    Here is full code (that's not working)

    Code:
    Private Sub CommandButton5_Click()
    'Load Selections Button
    
    '***************
    'Check to make sure selections were made in each catagory
    
    If ActiveSheet.Range("J5").Value < 1 Then
    MsgBox "You have not selected any ItemID/Parts, please make at least one selection"
    Else
    
    If ActiveSheet.Range("B5").Value > 1 Then
    MsgBox "Select only One Buyer"
    Else
    
    
    If ActiveSheet.Range("B5").Value < 1 Then
    MsgBox "You must select a Buyer"
    Else
    
    
    If ActiveSheet.Range("F5").Value > 1 Then
    MsgBox "Select only One Vendor"
    Else
    
    If ActiveSheet.Range("F5").Value < 1 Then
    MsgBox "You must select a Vendor"
    Else
    
    '*********************
    'Load ItemId Selections to CMOPUpload Sheet
    'Filter Selected Items (Yes)and then Copy and Paste to the next avaiable Row (B) on the CMOP
    
    Dim lstrw As Long
    Dim lstrwI As Long
    Dim lstrwB As Long
    
    lstrw = Sheets("CMOPUpload").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    
        ActiveSheet.Range("J5").Select
            ActiveCell.FormulaR1C1 = "=COUNTIF(C[4],""Yes"")"
            If ActiveSheet.Range("J5").Value > 0 Then
    
        ActiveSheet.ListObjects("ModelGeneral_vluItem__2").Range.AutoFilter Field:=6 _
            , Criteria1:="Yes"
     
        Range("I7").Select
        ActiveCell.Offset(1, 0).Select
        
        Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
        Sheets("CMOPUpload").Range("A" & lstrw).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    
        Worksheets("Deltek_Selections").Activate
        ActiveSheet.ListObjects("ModelGeneral_vluItem__2").Range.AutoFilter Field:=6
        
    '*********************
    'Load BuyerId Selection to CMOPUpload Sheet
    'Filter Selected Buyer (Yes)and then Copy and Paste to the next avaiable Row (F) on the CMOP
    
    
    lstrwI = Sheets("CMOPUpload").Range("A" & Rows.Count).End(xlUp).Row
    lstrwB = Sheets("CMOPUpload").Range("F" & Rows.Count).End(xlUp).Row
    
        ActiveSheet.Range("B5").Select
            ActiveCell.FormulaR1C1 = "=COUNTIF(C[1],""Yes"")"
            If ActiveSheet.Range("B5").Value > 0 Then
    
        ActiveSheet.ListObjects("ModelGeneral_vluBuyer__2").Range.AutoFilter Field:=3 _
            , Criteria1:="Yes"
     
        Range("A7").Select
        ActiveCell.Offset(1, 0).Select
     
        Range(Selection, Selection.End(xlDown).Offset(0, 1)).Copy
        Destination = Sheets("CMOPUpload").Range(Cells(lstrwB + 1, "F"), Cells(lstrwI, "F"))
        
    
       ' Application.CutCopyMode = False
        
    
    
    
        Worksheets("Deltek_Selections").Activate
        ActiveSheet.ListObjects("ModelGeneral_vluBuyer__2").Range.AutoFilter Field:=3
    
          
    
        
    
        
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    
    
    
    
    
    
    
    
    
    
    
    
    End Sub
    Last edited by Fluff; Jul 9th, 2019 at 02:38 PM. Reason: Corrected code tags
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,449
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Copy and Paste down to last Row +

    Quote Originally Posted by gheyman View Post
    Thanks JoeMo

    I tried to modify your code to fit mine without success. (see last row of code

    [code]
    Dim lstrwI As Long
    Dim lstrwB As Long

    lstrwI = Sheets("CMOPUpload").Range("A" & Rows.Count).End(xlUp).Row
    lstrwB = Sheets("CMOPUpload").Range("F" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("B5").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[1],""Yes"")"
    If ActiveSheet.Range("B5").Value > 0 Then

    ActiveSheet.ListObjects("ModelGeneral_vluBuyer__2").Range.AutoFilter Field:=3 _
    , Criteria1:="Yes"

    Range("A7").Select
    ActiveCell.Offset(1, 0).Select

    Range(Selection, Selection.End(xlDown).Offset(0, 1)).Copy
    Sheets("CMOPUpload").Range(Cells(lstrwB + 1, "F"), Cells(lstrwI, "F")).PasteSpecial xlPasteValues

    End If

    [code/]
    If you are going to do a pastespecial from a range you copied, you only need to provide the upper left-most cell of the destination range. In your case that's:
    Code:
    Sheets("CMOPUpload").Cells(lstrwB + 1, "F").PasteSpecial xlPasteValues
    The description "code is not working" provides no useful information. To diagnose the problem(s), more detail on exactly what does or doesn't happen when you try to run the code is needed.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

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
  •