macro to copy only filled cells in rows help

mrclox

New Member
Joined
Nov 24, 2005
Messages
46
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

doco

Active Member
Joined
May 4, 2006
Messages
262
Code:
    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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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.

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

I would use something like

Code:
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
 

Forum statistics

Threads
1,181,420
Messages
5,929,801
Members
436,696
Latest member
Mr Rice

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top