Copy and Paste down to last Row +

[gheyman]

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!

 

[JoeMo]

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

 

[gheyman]

Thanks JoeMo

I tried to modify your code to fit mine without success. (see last row of 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/]

 

[gheyman]

Here is full code (that's not working)

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

 

[JoeMo]

Thanks JoeMo

I tried to modify your code to fit mine without success. (see last row of 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:

	
	
	
	
	
	



	
	

		
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.