Sumay Totals Sheets

[Manchesterisred]

I need some with my macrothat will look at all worksheets and then create a summary on the a Worsheet called "Summary"

At the moment, it creates a header and then looks at the same cell values on each worksheet and then copies it to the summary. The problem is that i am getting a scattered reponses in teh summary and its not uniform in a table going down wards. Sometimes even outside the table, like this:

	Mandate Number	Company Name	Payment Term Days	Payment Value Date	Amount Per DD Letter	Amount Per SAP Extract	Variance
Sheet 1	0​	0​	0​	0​	0​	0​	0​	0​
Sheet 2	0​	0​	0​	0​	0​	0​	0​	0​
Sheet 3	0​	0​	0​	0​	0​	0​	0​	0​
Sheet 4	0​	0​	0​	0​	0​	0​	0​	0​
Sheet 5	0​	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Sheet 6	Yes	0​	0​	0​	0​	0​	0​	0​

Here is my macros which isnt the best - so hoping someone can help me: Thank you

Sub DDTotals()
'
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Copy Data to Summary sheet" if it exist
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets("Summary")
Newsh.Rows("2:" & Newsh.Rows.Count).Clear

'Add headers
Newsh.Range("B1:H1").Value = Array("Mandate Number", "Company Name", "Payment Term Days", "Payment Value Date", "Amount Per DD Letter", "Amount Per SAP Extract", "Variance")

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("E21,B2:E22,E20") '<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub