How do I copy all visible cells with vba code?

Question

I have a large data set. In column A I have branch codes. I have filtered my data according to a specific branch code. Now lets say there are 3300 lines left after filtering my data. If I select all, F5, visible cells only, and I copy and paste the data to a new sheet, it only copies about 300 lines. (In VBA and when I do it manually) Why is this happening? Is there a way to adapt my code so that it copies all of the data to a new sheet?

I will show my code below

Sheets("Open Orders").Select
Range("Table_OpenOrders[[#Headers],[Branch Plant]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.copy
Sheets("Copy Open Orders").Select
Range("A1").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks 
 _
          :=False, Transpose:=False
Columns("D:F").Select
Application.CutCopyMode = False

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, Jul 23 '19 at 07:55

score 1 · Answer 1 · answered Jul 23 '19 at 08:24

1

This is the code which will copy all the visible cells to Copy Open Orders from Open Orders.

sheets("Open Orders").Range("A1:AA100000").SpecialCells(xlCellTypeVisible).Copy
Sheets("Copy Open Orders").PasteSpecial xlPasteValues

answered Jul 23 '19 at 08:24

wisniadj

106
7

How many rows does your spreasheet have before filtering? – wisniadj Jul 23 '19 at 12:17
Problem solved thank you – Angie Jul 23 '19 at 12:34

How do I copy all visible cells with vba code?

1 Answers1