Hi,
I do not get error91 at my side.
Can you please copy this latest code to a new module and try again? Thanks.
VBA Code:
Option Explicit
Sub MergeSheets5()
Dim wsD As Worksheet
Dim ws As Worksheet
Set wsD = ThisWorkbook.Sheets("Wardwise")
'delete previous data
wsD.Range("B6:R10000").Clear
Dim data_lastrow As Long
Dim FoundCell As Range
Dim data_lastrow1 As Long
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Wardwise" Then
Else
'unmerge all cells
ws.Range("B8:R10000").UnMerge
'count the lastrow of each sheets
data_lastrow = wsD.Cells(Rows.Count, 4).End(xlUp).Row + 1
'Find net total as last row
Const WHAT_TO_FIND As String = "Net Total"
Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)
data_lastrow1 = FoundCell.Row
'copy sheets into Destination sheet
ws.Range("C8:Q" & data_lastrow1).Copy Destination:=wsD.Range("D" & data_lastrow)
ws.Range("B8:B" & data_lastrow1).Copy Destination:=wsD.Range("B" & data_lastrow)
wsD.Range("C" & data_lastrow & ":C" & data_lastrow + data_lastrow1 - 8).Value = ws.Name
End If
Next ws
data_lastrow = wsD.Cells(Rows.Count, 3).End(xlUp).Row + 1
'remove row contains TOTAL
Dim d As Long 'row number
d = 6
Do Until d = data_lastrow 'loop through each row
If wsD.Cells(d, 2).Value Like "*" & "Total" & "*" Then
wsD.Rows(d).EntireRow.Delete
Else
d = d + 1
End If
Loop
'refresh last row of destination sheet
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlYes
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlYes
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlYes
wsD.AutoFilterMode = False
Set wsD = Nothing
Set ws = Nothing
MsgBox "Done merged"
End Sub
Thank you for the reply. It's working fine.
Can you do a final help?
In the sorted Wardwise Sheet After Each ward
1. Insert one row for Summing up the rows above (Merge first three cells and Call this as
1st Ward Sub Total (may be named accodrding to the Ward Number))
2. Below this Main Roll Total, Add a row for Additional Total, if there exists Addition, merge the first three cells of the row ( Name this as
1st Ward Additional Total and sum up the rows just above this row)
3. Add a row below this row and call it as Deletion Total if there exists Deletion, merge the first three cells of the row ( Name this as
1st Ward Deletion Total and sum up the rows just above this row)
4. Below this
Deletion Row Add a row and call it as
Ist Ward Grand Total. In this row the formula should be
Ist Ward Sub Total +
Ist Ward Additional Total -
Ist Ward Deletion Total )
Repeat this for each and every ward (Please note that No.of Wards is not fixed and do not hard code it. Also Addition and Deletion may not be avaliable for all the wards . If it is there a row must be inserted to include in the calculation, otherwise no need to insert a row)
5. After completion of above procedure for all the wards a row at the bottom should be inserted and named it as
Net Total. In this row
Grand Total of all the wards should be added.
6. Print Area should be set from Columns
B to
P.
7. Page Break should be set after Grand Total of Each Ward.
Please refer to the spread sheet ()Wardwise Sheet) in which I have marked the requirements in Yellow Color.
File for Merge Test
Thank you once again.
