Copy paste methods

Hello,

 

I am having some problems trying to find the mistake I am making with the following code. I wrote this code to filter some data in field 13 copy that data and paste it into a a sheet called Brokered trades and then go back to today`s sheet and filter on field 12 and delete this criteria. With the remaining data I want to copy what is left and paste to brokered trades also.

 

I have this code split here into two sections Section 1 is giving me the problem on line 'ActiveCell.Offset(1, 0).Paste .

 Section 2 is a replica of the first part of the code and works fine. Any pointers would be greatly appreciated

 

Dim lastrow As Integer
Dim RNG As Range
Dim Numb_row As Integer
Dim RNGA As Range
Dim RNGA1 As Range
Dim sheetname As String

    SECTION 1

    Application.ScreenUpdating = False
    Application.CutCopyMode = False

     lastrow = Range("L1").End(xlDown).Row
     sheetname = Format(Date, "mmmdd")
     Sheets(sheetname).Range("A1:Q" & lastrow).Select
   
     'Selection.AutoFilter
     'Selection.AutoFilter Field:=13, Criteria1:="OSE", Operator:=xlAnd
     'Set RNGA = Sheets(sheetname).Range("A1:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
     'Sheets(sheetname).Range("A1:Q" & lastrow).Offset(1, 0).Copy
     'Selection.AutoFilter
     
     'Sheets("brokered trades").Select
     'Cells(Rows.Count, 1).End(xlUp).Select
     'ActiveCell.Offset(1, 0).Paste

      Section 2

     Sheets(sheetname).Range("A1:Q" & lastrow).Select
     Selection.AutoFilter
     Selection.AutoFilter Field:=12, Criteria1:="JPY", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="AUD", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="ZAR", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="CNY", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="NZD", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter
     Set RNG = Sheets(sheetname).Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row)
     Numb_row = RNG.SpecialCells(xlVisible).Count - 1
     MsgBox "Count  " & Numb_row & " broker trades to check"
     Set RNGA1 = Sheets(sheetname).Range("A1:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
     Sheets(sheetname).Range("A1:Q" & lastrow).SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy
     Sheets("brokered trades").Select
     Cells(Rows.Count, 1).End(xlUp).Select
     ActiveCell.Offset(1, 0).PasteSpecial
     Columns("A:Q").EntireColumn.AutoFit

What is the error message or symptom of your "problem?"

Please repost your code without commenting out the lines you want.

Dim lastrow As Integer
Dim RNG As Range
Dim Numb_row As Integer
Dim RNGA As Range
Dim RNGA1 As Range
Dim sheetname As String

   
    Application.ScreenUpdating = False
    Application.CutCopyMode = False

     lastrow = Range("L1").End(xlDown).Row
     sheetname = Format(Date, "mmmdd")
     Sheets(sheetname).Range("A1:Q" & lastrow).Select 
    
     Selection.AutoFilter 
     Selection.AutoFilter Field:=13, Criteria1:="OSE", Operator:=xlAnd 
     Set RNGA = Sheets(sheetname).Range("A1:Q" & Range("Q" & Rows.Count).End(xlUp).Row) 
     Sheets(sheetname).Range("A1:Q" & lastrow).Offset(1, 0).Copy 
     Selection.AutoFilter 
     Sheets("brokered trades").Select 
     Cells(Rows.Count, 1).End(xlUp).Select 
     ActiveCell.Offset(1, 0).Paste
     Sheets(sheetname).Range("A1:Q" & lastrow).Select
     Selection.AutoFilter
     Selection.AutoFilter Field:=12, Criteria1:="BNPAFRA", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="PARBPAR", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="BPCMTYO", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="STABPAR", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter Field:=12, Criteria1:="STASPAR", Operator:=xlAnd
     Selection.Offset(1, 0).Delete Shift:=xlUp
     Selection.AutoFilter
     Set RNG = Sheets(sheetname).Range("L1:L" & Range("L" & Rows.Count).End(xlUp).Row)
     Numb_row = RNG.SpecialCells(xlVisible).Count - 1
     MsgBox "Count  " & Numb_row & " broker trades to check"
     Set RNGA1 = Sheets(sheetname).Range("A1:Q" & Range("Q" & Rows.Count).End(xlUp).Row)
     Sheets(sheetname).Range("A1:Q" & lastrow).SpecialCells(xlCellTypeVisible).Offset(1, 0).Copy
     Sheets("brokered trades").Select
     Cells(Rows.Count, 1).End(xlUp).Select
     ActiveCell.Offset(1, 0).PasteSpecial
     Columns("A:Q").EntireColumn.AutoFit

sorry I posted before I could write a msg. The error message is saying object required or does not support this method. It seems strange because I have the same code further down which works fine.

 

JL

And what is the error message or symptom of the "problem" with the Paste method?

If you reply to yourself, you're the only one who gets emailed...

I'm not going to attempt to parse that interesting code.  As you seem to have created much of it by using the macro recorder, try recording again the steps you are automating and look at the code that creates.  I suspect the problem has something to do with invoking the Copy method and then filtering and selecting before finally using the Paste method.

Thank you, Understood.  I didn`t record the code but wrote it using help from a text book. I will follow your suggestion and hopefully find where I am going wrong. Thank you once again for the help. JL

If that code is from a textbook, you need to get a different textbook.