VBA Exclude ROWS from copying to other sheets

[nimesh29]

Hi All-
I am trying to create Master List (sheet) that will merge all other sheets in the workbook. At same time I need it do following, i got first one to work.

1. Exclude several sheets from showing up in Master list - Done
2. Exclude Heading rows from all these sheets when i bring it into master list, it would be row 1-6. Need help figuring this out.

This the code I have being working with.

Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name And ws.Name <> Sheets("CSI List").Name And ws.Name <> Sheets("List").Name Then
ws.UsedRange.Copy
With Range("A65536").End(xlUp).Offset(1)
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


End With
End If
Next
End Sub

Thanks in advance for your help!
Nimesh

 

[VoG]

Try

ws.UsedRange.Offset(6).Copy

 

[nimesh29]

Thanks VoG!
that works great.

-Nimesh

 

[nimesh29]

Vog,
how would i go about telling it to skip a row after it copies Data from each sheet?

Nimesh

 

[VoG]

Try

With Range("A65536").End(xlUp).Offset(2)

 

[nimesh29]

Try

With Range("A65536").End(xlUp).Offset(2)

Thanks Vog, that did it.

Cheers,
-Nimesh