Copy UsedRange except for Header

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,278
Try this. It copies the used range except for the header row from Sheet1 to Sheet2.
Code:
Sub CopyUsedRange()
    
    Sheets("Sheet2").Cells.ClearContents
    
    With Sheets("Sheet1").UsedRange
        .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy Sheets("Sheet2").Range("A1")
    End With
    
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
Here's another one.

Code:
Dim rng
Set rng = Worksheets("Sheet1").UsedRange
Intersect(rng, rng.Offset(1)).Copy Worksheets("Sheet2").Range("A2")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,936
Messages
5,471,633
Members
406,773
Latest member
Lunacy4682

This Week's Hot Topics

Top