Changing the condition of a For Each loop for a single instance

Hey guys,

Witht he help of Arthur I got the following macro and edited it so it would for for my behalf. Now, I tried what I was about to ask a few times, and it never did what I hoped it would.

What I was hoping, is this: StartRow is defined as 2. That is correct, and I want that to be the case for all sheets aside from the first one it loops through (to copy the headers).

Is it possible to set a conditional startRow that for the first execution StartRow = 1, else StartRow = 2?

Thanks in advance for your help!

Best,
Andy

Sub CopyDataWithoutHeaders()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("SU11 Master File").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "SU11 Master File"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "SU11 Master File"

'Fill in the start row
StartRow = 2

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets


'Loop through all worksheets except the Summer Master File worksheet and the
'Information worksheet, you can ad more sheets to the array if you want.
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Run Compilation"), 0)) Then


'Find the last row with data on the DestSh and sh
Last = LastRow(DestSh)
shLast = LastRow(sh)

'If sh is not empty and if the last row >= StartRow copy the CopyRng
If shLast > 0 And shLast >= StartRow Then

'Set the range that you want to copy - **Add info to remove exact sizes from individual sheets
Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast))

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look below example 1 on this page
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

End If

End If
Next

ExitTheSub:

Application.GoTo DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Hey Andy,

I tried integrating the code into the previous one (yes it worked previously), and I also tried it by itself, in one version I got a Syntax error, when I used just yours I got 'Invalid Outside Procedure' referring to the StartRow =1.

Also, remember that being able to define the destination worksheet for the macro is quite important, so if you snip the code please don't remove that.

Thanks in advance for all your help.

Best,
Andy

Hey Andy,

Thanks again, the code runs again now, however, my first worksheet that feeds into it (which is sheet one, or alternatively named 'Roughwear') is still starting the copying at the second line.

Maybe we can determine for

For worksheet.Name = "Roughwear"
StartRow = 1
else

....

Would that work?

It was my original attempt but it didn't work out.

Thanks in advance!

Andy

Andy:

Fantastic: That's exactly what i was trying to do earlier but it wasn't working correctly.

You made it work, thanks so much for your help. I am now a happy camper.

Enjoy your evening.