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

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

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

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

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

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

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

Dave,
I ran the file and the following line had an error. Set MWS =
mySheets(sCtr)

How do I insert the Option Explicit into the marco? Or is it required in the
code because it always appears above the line that seperates the marcos .

As for your question at the bottom of the code: "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." I get the file from a download on the web so it
is not a file I create, I am simply required to modify the file and generate
a report from the available data. If I run a macron on the workbook which
has up to 30 sheets I don't know if the sheet exists until an error is
generated, so I wanted to prevent that from happening.

Is there any other information I could provide you that would make my
request more clearer and easier to help with?

I do appreciate your efforts I am only sorry for the fact that I don't
understand how to do this stuff better myself..

Joe

"Dave Peterson" wrote in message
...
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

Option Explicit

goes above everything else--it appears only once per module. It tells excel
that you will declare every variable that you use in that module.

I had a typo. Use this instead:
Set MWS = Sheets(mySheets(sCtr))

If you get other errors, please include the error message and the line that
caused the error.

My question was about these lines in your original code.

Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

....
Set AWS = ActiveSheet

So you select ray517, you activate it and then later you want to ignore that
sheet with this line:

If Not MWS Is AWS Then

Why not just use:
Sheets(Array("RAY518, RAY519")).Select

===
I think it's better to avoid selecting, though.



Joe wrote:

Dave,
I ran the file and the following line had an error. Set MWS =
mySheets(sCtr)

How do I insert the Option Explicit into the marco? Or is it required in the
code because it always appears above the line that seperates the marcos .

As for your question at the bottom of the code: "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." I get the file from a download on the web so it
is not a file I create, I am simply required to modify the file and generate
a report from the available data. If I run a macron on the workbook which
has up to 30 sheets I don't know if the sheet exists until an error is
generated, so I wanted to prevent that from happening.

Is there any other information I could provide you that would make my
request more clearer and easier to help with?

I do appreciate your efforts I am only sorry for the fact that I don't
understand how to do this stuff better myself..

Joe

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

This worked B E A U T I F U L thank you very much for the help. I understand
your question about selecting pages in the original code. I should have
removed those entries before posting as those entries were left over from my
failed attempts to create a code myself.

Joe
"Dave Peterson" wrote in message
...
Option Explicit

goes above everything else--it appears only once per module. It tells
excel
that you will declare every variable that you use in that module.

I had a typo. Use this instead:
Set MWS = Sheets(mySheets(sCtr))

If you get other errors, please include the error message and the line
that
caused the error.

My question was about these lines in your original code.

Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

...
Set AWS = ActiveSheet

So you select ray517, you activate it and then later you want to ignore
that
sheet with this line:

If Not MWS Is AWS Then

Why not just use:
Sheets(Array("RAY518, RAY519")).Select

===
I think it's better to avoid selecting, though.



Joe wrote:

Dave,
I ran the file and the following line had an error. Set MWS =
mySheets(sCtr)

How do I insert the Option Explicit into the marco? Or is it required in
the
code because it always appears above the line that seperates the marcos .

As for your question at the bottom of the code: "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." I get the file from a download on the web so
it
is not a file I create, I am simply required to modify the file and
generate
a report from the available data. If I run a macron on the workbook which
has up to 30 sheets I don't know if the sheet exists until an error is
generated, so I wanted to prevent that from happening.

Is there any other information I could provide you that would make my
request more clearer and easier to help with?

I do appreciate your efforts I am only sorry for the fact that I don't
understand how to do this stuff better myself..

Joe

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

Glad it works.



Joe wrote:

This worked B E A U T I F U L thank you very much for the help. I understand
your question about selecting pages in the original code. I should have
removed those entries before posting as those entries were left over from my
failed attempts to create a code myself.

Joe
"Dave Peterson" wrote in message
...
Option Explicit

goes above everything else--it appears only once per module. It tells
excel
that you will declare every variable that you use in that module.

I had a typo. Use this instead:
Set MWS = Sheets(mySheets(sCtr))

If you get other errors, please include the error message and the line
that
caused the error.

My question was about these lines in your original code.

Sheets(Array("RAY517", "RAY518, RAY519")).Select
Sheets("RAY517").Activate

...
Set AWS = ActiveSheet

So you select ray517, you activate it and then later you want to ignore
that
sheet with this line:

If Not MWS Is AWS Then

Why not just use:
Sheets(Array("RAY518, RAY519")).Select

===
I think it's better to avoid selecting, though.



Joe wrote:

Dave,
I ran the file and the following line had an error. Set MWS =
mySheets(sCtr)

How do I insert the Option Explicit into the marco? Or is it required in
the
code because it always appears above the line that seperates the marcos .

As for your question at the bottom of the code: "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." I get the file from a download on the web so
it
is not a file I create, I am simply required to modify the file and
generate
a report from the available data. If I run a macron on the workbook which
has up to 30 sheets I don't know if the sheet exists until an error is
generated, so I wanted to prevent that from happening.

Is there any other information I could provide you that would make my
request more clearer and easier to help with?

I do appreciate your efforts I am only sorry for the fact that I don't
understand how to do this stuff better myself..

Joe

"Dave Peterson" wrote in message
...
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

--

Dave Peterson

--

Dave Peterson