Help with Worksheet Merging Macro

[Joe]

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 Moehrbach]

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

 

[Dave Peterson]

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)

 

[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.

 

[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]

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 procedure:

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]

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

 

[Otto Moehrbach]

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

 

[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.

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

 

[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

 

[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]

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.

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.

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

 

[Joe]

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]

Glad it works.

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