I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one minor
problem. I have to crate this report on a monthly basis and all three of the
worksheets do not exist every month. I no there has to be a way to attach a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?


Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate


Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

Joe
What I usually do is use a "For" loop statement like:
For each ws in ThisWorkbook.Worksheets
That would include every sheet in the file. If you wanted to exclude one or
more sheets, say sheets "One" and "Two", you could use a statement like:
If ws.Name<"One" And ws.Name<"Two" Then
'Your code would go here.
End if
If every sheet that you wanted to operate on is named "RAY......." and you
want to exclude all others, you could use a statement like:
If Left(ws.Name,3) = "RAY" Then
'Your code would go here.
End If
HTH Otto
"Joe" wrote in message
...
I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one minor
problem. I have to crate this report on a monthly basis and all three of
the worksheets do not exist every month. I no there has to be a way to
attach a statement that checks to see that each tab exists before
proceeding or existing the macro becasue of an error. Could someone please
help?


Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate


Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

Thank you for responding but I guess I am a little dense because I don't get
it. I am new to excel and what I know is self taught so it takes me sometime
to learn things. I don't understand how the For statement is used to prevent
an error. FYI-not all of my worksheets are labeled as RAY* and this is just
one of the many merges I would be performing on the workbook. Thought if I
could get one macro working I could perform the others myself.


"Otto Moehrbach" wrote in message
...
Joe
What I usually do is use a "For" loop statement like:
For each ws in ThisWorkbook.Worksheets
That would include every sheet in the file. If you wanted to exclude one
or more sheets, say sheets "One" and "Two", you could use a statement
like:
If ws.Name<"One" And ws.Name<"Two" Then
'Your code would go here.
End if
If every sheet that you wanted to operate on is named "RAY......." and you
want to exclude all others, you could use a statement like:
If Left(ws.Name,3) = "RAY" Then
'Your code would go here.
End If
HTH Otto
"Joe" wrote in message
...
I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one minor
problem. I have to crate this report on a monthly basis and all three of
the worksheets do not exist every month. I no there has to be a way to
attach a statement that checks to see that each tab exists before
proceeding or existing the macro becasue of an error. Could someone please
help?


Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate


Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

Joe
The "For" loop simply goes from one sheet to the next and so on until it
has gone through all of them. For each sheet it will do whatever the code
says. The operating code is what goes between the "For" statement and the
"Next ws" statement. That code doesn't get into the names of the sheets so
it doesn't matter what sheets are there and what sheets are not there. I
included some code to exclude some sheets if you need to do that. Even that
exclusionary code doesn't care if those sheets (the sheets you want to
exclude) actually exist or not. Post back if you want more info. Otto
"Joe" wrote in message
...
Thank you for responding but I guess I am a little dense because I don't
get it. I am new to excel and what I know is self taught so it takes me
sometime to learn things. I don't understand how the For statement is used
to prevent an error. FYI-not all of my worksheets are labeled as RAY* and
this is just one of the many merges I would be performing on the workbook.
Thought if I could get one macro working I could perform the others
myself.


"Otto Moehrbach" wrote in message
...
Joe
What I usually do is use a "For" loop statement like:
For each ws in ThisWorkbook.Worksheets
That would include every sheet in the file. If you wanted to exclude one
or more sheets, say sheets "One" and "Two", you could use a statement
like:
If ws.Name<"One" And ws.Name<"Two" Then
'Your code would go here.
End if
If every sheet that you wanted to operate on is named "RAY......." and
you want to exclude all others, you could use a statement like:
If Left(ws.Name,3) = "RAY" Then
'Your code would go here.
End If
HTH Otto
"Joe" wrote in message
...
I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one
minor problem. I have to crate this report on a monthly basis and all
three of the worksheets do not exist every month. I no there has to be a
way to attach a statement that checks to see that each tab exists before
proceeding or existing the macro becasue of an error. Could someone
please help?


Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate


Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

Otto,
The routine already has a For loop so where would I place this new loop?
I have inserted it into multiple locations and cannot seem to make it work.
Thanks for the help.

Joe

"Otto Moehrbach" wrote in message
...
Joe
The "For" loop simply goes from one sheet to the next and so on until
it has gone through all of them. For each sheet it will do whatever the
code says. The operating code is what goes between the "For" statement
and the "Next ws" statement. That code doesn't get into the names of the
sheets so it doesn't matter what sheets are there and what sheets are not
there. I included some code to exclude some sheets if you need to do
that. Even that exclusionary code doesn't care if those sheets (the
sheets you want to exclude) actually exist or not. Post back if you want
more info. Otto
"Joe" wrote in message
...
Thank you for responding but I guess I am a little dense because I don't
get it. I am new to excel and what I know is self taught so it takes me
sometime to learn things. I don't understand how the For statement is
used to prevent an error. FYI-not all of my worksheets are labeled as
RAY* and this is just one of the many merges I would be performing on the
workbook. Thought if I could get one macro working I could perform the
others myself.


"Otto Moehrbach" wrote in message
...
Joe
What I usually do is use a "For" loop statement like:
For each ws in ThisWorkbook.Worksheets
That would include every sheet in the file. If you wanted to exclude
one or more sheets, say sheets "One" and "Two", you could use a
statement like:
If ws.Name<"One" And ws.Name<"Two" Then
'Your code would go here.
End if
If every sheet that you wanted to operate on is named "RAY......." and
you want to exclude all others, you could use a statement like:
If Left(ws.Name,3) = "RAY" Then
'Your code would go here.
End If
HTH Otto
"Joe" wrote in message
...
I found the following post for Merging Excel worksheets and after I
added the Array statement it works great for my application except for
one minor problem. I have to crate this report on a monthly basis and
all three of the worksheets do not exist every month. I no there has to
be a way to attach a statement that checks to see that each tab exists
before proceeding or existing the macro becasue of an error. Could
someone please help?


Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate


Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

You could loop through the list and build an array of names when the sheets
exist.

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant
Dim MWS As Variant 'not just worksheets

mySheetNames = Array("RAY517", "RAY518, RAY519")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For Each MWS In mySheets
MsgBox MWS
Next MWS

'or I like this way...
For sCtr = LBound(mySheets) To UBound(mySheets)
MsgBox mySheets(sCtr)
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

Joe wrote:

I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one minor
problem. I have to crate this report on a monthly basis and all three of the
worksheets do not exist every month. I no there has to be a way to attach a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

--

Dave Peterson

Dave,
As I pointed out to Otto above I am a self taught excel person and I
simply don't understand the code and boy was I really thrown for a loop with
the function section. Totally lost there. I do appreciate you help but I
cannot determine how to use the code at this point.

Joe

"Dave Peterson" wrote in message
...
You could loop through the list and build an array of names when the
sheets
exist.

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant
Dim MWS As Variant 'not just worksheets

mySheetNames = Array("RAY517", "RAY518, RAY519")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For Each MWS In mySheets
MsgBox MWS
Next MWS

'or I like this way...
For sCtr = LBound(mySheets) To UBound(mySheets)
MsgBox mySheets(sCtr)
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

Joe wrote:

I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one
minor
problem. I have to crate this report on a monthly basis and all three of
the
worksheets do not exist every month. I no there has to be a way to attach
a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

--

Dave Peterson

Sometimes developers want to check the same thing over and over. Instead of
writing inline code that does the same function multiple times, they'll
encapsulate the code into a function. That's what the code from Chip does.

You can put that function in any general module and check to see if a sheet
exists in any workbook with a line like this in your procedu

if sheetexists(activeworkbook, "Sheet1") then
'it exists
else
'it doesn't
end if

You really don't need to know how it works--although if you look at it, you'll
see that it just tries to find the name of the sheet. If the sheet doesn't
exist, then neither will the name.

As for the other code, it loops through your list of potential sheet names and
creates another array of just the names of existing sheets.

Try stepping through the code and see if you can see what's happening. Maybe
even add a watch for the mySheets variable.

Joe wrote:

Dave,
As I pointed out to Otto above I am a self taught excel person and I
simply don't understand the code and boy was I really thrown for a loop with
the function section. Totally lost there. I do appreciate you help but I
cannot determine how to use the code at this point.

Joe

"Dave Peterson" wrote in message
...
You could loop through the list and build an array of names when the
sheets
exist.

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant
Dim MWS As Variant 'not just worksheets

mySheetNames = Array("RAY517", "RAY518, RAY519")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For Each MWS In mySheets
MsgBox MWS
Next MWS

'or I like this way...
For sCtr = LBound(mySheets) To UBound(mySheets)
MsgBox mySheets(sCtr)
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

Joe wrote:

I found the following post for Merging Excel worksheets and after I added
the Array statement it works great for my application except for one
minor
problem. I have to crate this report on a monthly basis and all three of
the
worksheets do not exist every month. I no there has to be a way to attach
a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

--

Dave Peterson

--

Dave Peterson

Dave,
Again I thank you for all the help but I still do not understand how to
put the code you provided in to my existing coed. When I just use your code
the data doesn't merge, so I assume that something is missing. You are
right, we don't always need to understand how or why something works we just
need to know that it does. So if you could help just a little more and
explain to incorporate this code into my code would truly. Once again I say
thank you for all the help so far and the future help you may provide.

Joe

"Dave Peterson" wrote in message
...
Sometimes developers want to check the same thing over and over. Instead
of
writing inline code that does the same function multiple times, they'll
encapsulate the code into a function. That's what the code from Chip
does.

You can put that function in any general module and check to see if a
sheet
exists in any workbook with a line like this in your procedu

if sheetexists(activeworkbook, "Sheet1") then
'it exists
else
'it doesn't
end if

You really don't need to know how it works--although if you look at it,
you'll
see that it just tries to find the name of the sheet. If the sheet
doesn't
exist, then neither will the name.

As for the other code, it loops through your list of potential sheet names
and
creates another array of just the names of existing sheets.

Try stepping through the code and see if you can see what's happening.
Maybe
even add a watch for the mySheets variable.

Joe wrote:

Dave,
As I pointed out to Otto above I am a self taught excel person and I
simply don't understand the code and boy was I really thrown for a loop
with
the function section. Totally lost there. I do appreciate you help but I
cannot determine how to use the code at this point.

Joe

"Dave Peterson" wrote in message
...
You could loop through the list and build an array of names when the
sheets
exist.

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant
Dim MWS As Variant 'not just worksheets

mySheetNames = Array("RAY517", "RAY518, RAY519")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For Each MWS In mySheets
MsgBox MWS
Next MWS

'or I like this way...
For sCtr = LBound(mySheets) To UBound(mySheets)
MsgBox mySheets(sCtr)
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

Joe wrote:

I found the following post for Merging Excel worksheets and after I
added
the Array statement it works great for my application except for one
minor
problem. I have to crate this report on a monthly basis and all three
of
the
worksheets do not exist every month. I no there has to be a way to
attach
a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

--

Dave Peterson

--

Dave Peterson

Compiled, but not tested:

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Const NHR = 1

Set AWS = ActiveSheet

mySheetNames = Array("RAY517", "RAY518, RAY519", "sheet1")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For sCtr = LBound(mySheets) To UBound(mySheets)
Set MWS = mySheets(sCtr)
If MWS.Name < AWS.Name Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

========
Why would you include Ray517 in the array of sheets to be combined, but then not
use it. I'd just remove it from the array.


Joe wrote:

Dave,
Again I thank you for all the help but I still do not understand how to
put the code you provided in to my existing coed. When I just use your code
the data doesn't merge, so I assume that something is missing. You are
right, we don't always need to understand how or why something works we just
need to know that it does. So if you could help just a little more and
explain to incorporate this code into my code would truly. Once again I say
thank you for all the help so far and the future help you may provide.

Joe

"Dave Peterson" wrote in message
...
Sometimes developers want to check the same thing over and over. Instead
of
writing inline code that does the same function multiple times, they'll
encapsulate the code into a function. That's what the code from Chip
does.

You can put that function in any general module and check to see if a
sheet
exists in any workbook with a line like this in your procedu

if sheetexists(activeworkbook, "Sheet1") then
'it exists
else
'it doesn't
end if

You really don't need to know how it works--although if you look at it,
you'll
see that it just tries to find the name of the sheet. If the sheet
doesn't
exist, then neither will the name.

As for the other code, it loops through your list of potential sheet names
and
creates another array of just the names of existing sheets.

Try stepping through the code and see if you can see what's happening.
Maybe
even add a watch for the mySheets variable.

Joe wrote:

Dave,
As I pointed out to Otto above I am a self taught excel person and I
simply don't understand the code and boy was I really thrown for a loop
with
the function section. Totally lost there. I do appreciate you help but I
cannot determine how to use the code at this point.

Joe

"Dave Peterson" wrote in message
...
You could loop through the list and build an array of names when the
sheets
exist.

Option Explicit
Sub testme02()
Dim mySheetNames As Variant
Dim sCtr As Long 'sheet counter
Dim eCtr As Long 'exist counter
Dim mySheets() As Variant
Dim MWS As Variant 'not just worksheets

mySheetNames = Array("RAY517", "RAY518, RAY519")

ReDim mySheets(LBound(mySheetNames) To UBound(mySheetNames))

eCtr = LBound(mySheetNames) - 1
For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
If SheetExists(mySheetNames(sCtr), ActiveWorkbook) Then
eCtr = eCtr + 1
mySheets(eCtr) = mySheetNames(sCtr)
End If
Next sCtr

If eCtr < LBound(mySheetNames) Then
'no sheets exist!
Else
ReDim Preserve mySheets(LBound(mySheets) To eCtr)
For Each MWS In mySheets
MsgBox MWS
Next MWS

'or I like this way...
For sCtr = LBound(mySheets) To UBound(mySheets)
MsgBox mySheets(sCtr)
Next sCtr
End If

End Sub
Function SheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
SheetExists = CBool(Len(WB.Sheets(SheetName).Name) 0)
On Error GoTo 0
End Function

Joe wrote:

I found the following post for Merging Excel worksheets and after I
added
the Array statement it works great for my application except for one
minor
problem. I have to crate this report on a monthly basis and all three
of
the
worksheets do not exist every month. I no there has to be a way to
attach
a
statement that checks to see that each tab exists before proceeding or
existing the macro becasue of an error. Could someone please help?

Sub MergeSheets()

' Merges data from all the selected worksheets onto the end of the
' active worksheet.

Const NHR = 1 'Number of header rows to not copy from each MWS
Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

Dim MWS As Worksheet 'Worksheet to be merged
Dim AWS As Worksheet 'Worksheet to which the data are transferred
Dim FAR As Long 'First available row on AWS
Dim LR As Long 'Last row on the MWS sheets

Set AWS = ActiveSheet

For Each MWS In ActiveWindow.SelectedSheets
If Not MWS Is AWS Then
FAR = AWS.UsedRange.Cells(AWS.UsedRange.Cells.Count).Row + 1
LR = MWS.UsedRange.Cells(MWS.UsedRange.Cells.Count).Row
MWS.Range(MWS.Rows(NHR + 1), MWS.Rows(LR)).Copy AWS.Rows(FAR)
End If
Next MWS

End If

Joe

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson