Solved VBA copy paste filtered data to another sheet

September 12, 2018 at 03:23:54
Specs: Windows 7
Hi, I am trying to create code in VBA (within MSExcel) to filter out rows where column F is not blank and then select all of the data and paste into another sheet. The problem I am having is that all of the data is copied but only 38 rows are pasted into the other sheet rather than the 819 rows expected. I have tried running:

Sub NumRows()
 MsgBox Range("A" & Rows.Count).End(xlUp).Row
End Sub

The result is the correct number of rows as expected (800+). Any thoughts on why the amount of rows pasted across are limited to only 38?

Thanks

message edited by b1gb3n237


See More: VBA copy paste filtered data to another sheet

Reply ↓  Report •

#1
September 12, 2018 at 03:25:34
The current state of the code is:

Sub ExpiringNew()

    Sheets("Expiring Made Up").Visible = True
    Sheets("Expiring Made Up").Visible = True

'removes filter from previous search
    Sheets("Expiring Made Up").Select
    ActiveSheet.Range("$O$2:$O$35").AutoFilter Field:=1
    
'deletes old data in sheet

    Sheets("Expiring Made Up").Select
    Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Selection.Delete Shift:=xlUp

'filters by blank

    With Sheets("Making Up Reagent Log").Range("$F$3:$F$13322")
    .AutoFilter Field:=1, Criteria1:="="
       
'copy and pastes from one sheet to other
    
    Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
    ActiveSheet.Paste Destination:=Worksheets("Expiring Made Up").Range("A4")
      
    End With
        
'filters away not expiring within date range
    
    Sheets("Expiring Made Up").Select
    Range("O2:O3").Select
    Range("O3").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$O$2:$O$35").AutoFilter Field:=1, Criteria1:= _
    "<TODAY()+30", Operator:=xlAnd
       
'selects cell A1
       
    Sheets("Expiring Made Up").Select
    Range("A1").Select
    
End Sub

message edited by b1gb3n237


Reply ↓  Report •

#2
September 12, 2018 at 03:57:47
Before we answer your question, here's a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
September 13, 2018 at 12:59:34
✔ Best Answer
Before I look into your actual problem, I'm confused by parts of your code. Please
understand that we don't have a copy of your data, so it's a little hard for us to test
your code in a matching workbook.

I'm also going to offer some suggestions that might make your code more efficient,
such as eliminating a lot of the "Select" instructions. Rarely do you need to Select an
object to perform an action on it.

============================================================

Sub ExpiringNew()

    Sheets("Expiring Made Up").Visible = True
    Sheets("Expiring Made Up").Visible = True

Why 2 copies of the same instruction?
Is the .Visible instruction even needed? Is the sheet Hidden before the macro is run?

============================================================

'removes filter from previous search
    Sheets("Expiring Made Up").Select
    ActiveSheet.Range("$O$2:$O$35").AutoFilter Field:=1

Try this:

'removes filter from previous search
    Sheets("Expiring Made Up").Range("$O$2:$O$35").AutoFilter Field:=1

============================================================

'deletes old data in sheet

    Sheets("Expiring Made Up").Select
    Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Selection.Delete Shift:=xlUp

2 things to mention:

1 - That can be shortened to this:

'deletes old data in sheet

    Sheets("Expiring Made Up").Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Delete Shift:=xlUp

2 - What are you trying to do with the following?

Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row)

Let's say that you have data in A4:A100. The result of that line is going to be:

Range("A4:O4" & 100) then Range("A4:O4100")

Is that what you are looking for?

============================================================

'filters away not expiring within date range
    
    Sheets("Expiring Made Up").Select
    Range("O2:O3").Select
    Range("O3").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$O$2:$O$35").AutoFilter Field:=1, Criteria1:= _
    "<TODAY()+30", Operator:=xlAnd

I'm pretty sure that that can be written as:

'filters away not expiring within date range
    
    Sheets("Expiring Made Up").Range("$O$2:$O$35").AutoFilter Field:=1, Criteria1:= _
    "<TODAY()+30", Operator:=xlAnd

============================================================

'selects cell A1
       
    Sheets("Expiring Made Up").Select
    Range("A1").Select

That can be written as:

'selects cell A1
       
    Sheets("Expiring Made Up").Range("A1").Select

============================================================

OK, so now I believe that you could could look like this:

Sub ExpiringNew_v1()

    Sheets("Expiring Made Up").Visible = True

'removes filter from previous search
    Sheets("Expiring Made Up").Range("$O$2:$O$35").AutoFilter Field:=1
    
'deletes old data in sheet

    Sheets("Expiring Made Up").Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Delete Shift:=xlUp

'filters by blank

    With Sheets("Making Up Reagent Log").Range("$F$3:$F$13322")
    .AutoFilter Field:=1, Criteria1:="="
       
'copy and pastes from one sheet to other
    
    Range("A4:O4" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
    ActiveSheet.Paste Destination:=Worksheets("Expiring Made Up").Range("A4")
      
    End With
        
'filters away not expiring within date range
    
    Sheets("Expiring Made Up").Range("$O$2:$O$35").AutoFilter Field:=1, Criteria1:= _
    "<TODAY()+30", Operator:=xlAnd
       
'selects cell A1
       
    Sheets("Expiring Made Up").Select
    
End Sub

Try that and let us know if it acts the same way. If it does, look closely to see if the
data was actually pasted but is just not visible because a filter has been applied.
Perhaps the data is there, but there are hidden rows.

Have you tried to Single Step through the code via F8?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question