merging sheets to master sheet in a certain order

[marcia2026]

I am merging two sheets named "Previous Month" and "Current Month" together
on a master worksheet. I want them to sort to the master worksheet in that
order. What code do I need to add to my macro to accomplish this?

Thanks bunches in advance
marcia

 

[ward376]

Ron DeBruin has lots of good code on his site for stuff like this:
http://www.rondebruin.nl/tips.htm

Cliff Edwards

 

[marcia2026]

It is his code that I am using as part of my macro.
Here is what is happening:

'Delete "Previous" worksheet
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Previous").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Renames "Outstanding" to Previous
Sheets("Outstanding").Select
Sheets("Outstanding").Name = "Previous"


'Creates new "CurrentMonth" worksheet

Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim CopyRng As Range
Dim StartRow As Long
Dim i, j As Integer

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


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


'Add a worksheet with the name "CurrentMonth"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "CurrentMonth"

Then after "Current" is created, it goes directly into the merge of the two:

'Create "TotalForMonth" Worksheet

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

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

'Add a worksheet with the name "TotalForMonth"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "TotalForMonth"



I am not sure where to alter the code to specify the order of the merge

thanks
marcia

 

[ward376]

I don't see where any sheets are being merged - just sheets getting
deleted, created and renamed.

Are you running the code from the same workbook that the sheets are
in?

It looks like you're using "Outstanding" as the new "Previous", but
then you create a new "Current" sheet with no data - what do you want
to merge on the "Total" sheet?

If you post all your code and explain where the data is coming from
and exactly how you want to manipulate it, we can probably fix you up
pretty quickly.

Cliff Edwards

 

[marcia2026]

The whole macro is really long, but I think that I have managed a workaround.
There may be a shorter way, but here is what is happening:

1. open workbook for "Nov"
2. save as "Dec"
3. delete Nov "Previous" items
4. change Nov month end outstanding to "Previous" for Dec.
5. create Dec's "CurrentMonth" items
6. copy CurrentMonth & Previous to "TotalForMonth" (this is where I need the
items to go onto the new sheet in the particular order because I am going to
determine which has been resolved during Dec and then create "outstanding"
for Dec.

My workaround on the "TotalForMonth" is this:

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

'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "CurrentMonth"),0 )
Then
'Find the last row with data on the DestSh and sh
Last = lastrow(DestSh)
shLast = lastrow(sh)

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

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

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

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

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

Next


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


'Loop through the worksheets required
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Previous ), 0)) Then

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

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

'Set the range that you want to copy
Set CopyRng = sh.Range(sh.Rows(2), sh.Rows(shLast))

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

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

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "J").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If

End If

It seems to be working. I would like to know if there is a shorter way.