Copying and Pasting filtered data

[pdchislett]

Hi,

I'm trying to copy filtered data to another workbook.

I've tried using the following code which has worked before when the Filter headings are on row 1 but in this case the headings are on row 5.

Range("A5").Select

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy Workbooks(Upload_File).Worksheets("PAYMENTS").Range("A65536").End(xlUp).Offset(1, 0)

When the headings were on row 1 this just picked up the data rather than the headings and worked fine but this time it is picking up all the detail in rows 1-4 too.

Any ideas?

Thanks

 

[VoG]

Try

Dim LR As Long, LC As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(5, Columns.Count).End(xlToLeft).Column
Range(Cells(6, 1), Cells(LR, LC)).SpecialCells _
(xlCellTypeVisible).Copy Workbooks(Upload_File).Worksheets("PAYMENTS").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

 

[jim may]

Try ActiveSheet.CurrentRegion

versus

ActiveSheet.UsedRange

 

[pdchislett]

Thanks - worked a treat