macro to copy only filled cells in rows help

[mrclox]

I have a sheet with data in several columns and in the first 1000 or so rows is there a macro to copy just these filled rows and then paste to another sheet at the end of the filled rows in that sheet. Thanks in advance for any help

 

[doco]

    ActiveCell.CurrentRegion.Select
    Selection.Copy

'    considering your data begins A1 on sheet2
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1,0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Or something like that - without seeing your data the exact locations may be different by hopefully this gives the idea.

HTH

 

[royUK]

You don't need to select either the range to copy or the destination cell. There are some inbuilt methods such as, check VBA help for explanations.

Sheet1.UsedRange.Copy
'or
Sheet1.Cells(2,2).CurrentRegion.Copy

I would use something like

Sub copyData()
    Dim rToCopy As Range
    Dim rNextCl As Range
    'this will be next empty cell in ColumnA of sheet2
    Set rNextCl = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    With Sheet1
        'this copies columns A:E, amend 5 to what suits you
        Set rToCopy = .Range(.Cells(1, 1), .Cells(.Rows.Count, 5).End(xlUp))
    End With
    rToCopy.Copy rNextCl
End Sub