Copy table and paste to next empty row

[shophoney]

Hi I have a pivot table on sheet 1 and would like to paste values to the next empty row on sheet2.

I need a macro that will select everything but the header on line 1 and grab the data on the table and paste to the next empty line.

Thanks.

This is what i have so far, but it writes over the very last line from the last time i pasted. Also should i turn off screen updating as i will end up with a few million lines over time.





Sub CopyCurrentOH()
Dim ws As Worksheet
Dim rng As Range

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Sheets("OH History").Select
Application.Goto Reference:="R1C1"

Selection.End(xlDown).Select



Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Application.Goto Reference:="R1C1"

End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] CopyCurrentOH()
    
    Range("A2").CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Copy
    
    Sheets("OH History").Select
    Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = [color=darkblue]False[/color]
    
    Application.Goto Reference:="R1C1"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]