Copy and Paste down to last Row +

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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/]
 
Upvote 0
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 a moderator:
Upvote 0
Thanks JoeMo

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

Rich (BB 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/]
Rich (BB 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:

	
	
	
	
	
	


Rich (BB 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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top