VBA code Problem: Paste in the next available Row

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
Where I am pasting to is a table. My Titles in the Table (CMOPTable) start on Row 11 in B. Note the Table size will vary depending on what's being pasted each time.

This Pastes from the last Row up. So if the TABLE has 15 Rows in the table (and they are blank) and I select 3 items to Paste in; they paste in Rows 13, 14 and 15.

I need them to Past in the second 2 (of the table) and down.

Code:
Private Sub CommandButton1_Click()
'Load Button
'Filter Selected Items (Yes)and then Copy and Paste to the next avaiable Row (B) on the CMOP

Dim lstrw As Long

lstrw = Sheets("CMOPUpload").Range("B" & Rows.Count).End(xlUp).Offset(1).Row

    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[1],""Yes"")"

If Worksheets("ItemIDList").Range("E2").Value > 0 Then


    ActiveSheet.Range("A4").AutoFilter Field:=6, Criteria1:="Yes", Operator:=xlFilterValues
 
    Range("A3").Select
    ActiveCell.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
    Sheets("CMOPUpload").Range("B" & lstrw).PasteSpecial xlPasteValues

    Worksheets("ItemIDList").AutoFilterMode = False
    ActiveSheet.ListObjects("ModelGeneral_vluItem").Range.AutoFilter Field:=6
    Application.CutCopyMode = False
    
    Sheets("CMOPUpload").Activate
      Application.CutCopyMode = False
    
Else
    MsgBox "You have not selected any items to load"
    
End If


End Sub

Thank you for your time! Much appreciated
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,213,510
Messages
6,114,048
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