Master Worksheet with command Button to update data

[MajorK]

I need to increment the column index in the summary sheet each time I run the code or click the command button.
The Below code is able to do the job 1 time.
When I click the command button the Date should be added in the header, then the result of each sheet (row by row).
The result should look like the summary sheet in the attached picture. First time we run the macro: data displayed in Column B Second time: in Column C etc,..

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> Sub Worksheets_Summary()
Dim OldSheet As Worksheet
Dim NewSheet As Worksheet
Dim Cell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim book As Workbook
Set book = ThisWorkbook
Set NewSheet = book.Worksheets("Summary")
NewSheet.Rows("2:" & NewSheet.Rows.Count).Clear

RwNum = 1

For Each OldSheet In book.Worksheets
If OldSheet.Name <> "Summary" Then
Range("B1").Value = Now() 'Change B1
ColNum = 1
RwNum = RwNum + 1

NewSheet.Cells(RwNum, 1).Formula _
= "=HYPERLINK(""#""&CELL(""address"",'" & OldSheet.Name & "'!A1)," _
& """" & OldSheet.Name & """)"

For Each Cell In OldSheet.Range("B11")
ColNum = ColNum + 1
NewSheet.Cells(RwNum, ColNum).Formula = _
"='" & OldSheet.Name & "'!" & Cell.Address(False, False)
Next Cell

End If
Next OldSheet

NewSheet.UsedRange.Columns.AutoFit


End With
End Sub</code>Please Help!

 

[cooper645]

have modified your code as below. Have commented added or changed and highlighted them in red so you can see. also commented out a line of code as I couldn't figure out its purpose.

Sub Worksheets_Summary()
Dim OldSheet As Worksheet
Dim NewSheet As Worksheet
Dim Cell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim book As Workbook
[COLOR=#ff0000]Dim LastCol As Integer[/COLOR] [COLOR=#008000]'added[/COLOR]
Set book = ThisWorkbook
Set NewSheet = book.Worksheets("Summary")
[COLOR=#ff0000]'NewSheet.Rows("2:" & NewSheet.Rows.Count).Clear[/COLOR] '[COLOR=#008000]couldn't figure the purpose[/COLOR]
RwNum = 1
[COLOR=#ff0000]LastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1[/COLOR] [COLOR=#008000]'added[/COLOR]


For Each OldSheet In book.Worksheets
If OldSheet.Name <> "Summary" Then
[COLOR=#ff0000]Cells(1, LastCol)[/COLOR].Value = Now() 'Change B1 [COLOR=#008000]'CHANGED[/COLOR]
ColNum = 1
RwNum = RwNum + 1


NewSheet.Cells(RwNum, 1).Formula _
= "=HYPERLINK(""#""&CELL(""address"",'" & OldSheet.Name & "'!A1)," _
& """" & OldSheet.Name & """)"


For Each Cell In OldSheet.Range("B11")
ColNum = ColNum + 1
NewSheet.Cells(RwNum, [COLOR=#ff0000]LastCol[/COLOR]).Formula = _
"='" & OldSheet.Name & "'!" & Cell.Address(False, False) [COLOR=#008000]'CHANGED[/COLOR]
Next Cell


End If
Next OldSheet


NewSheet.UsedRange.Columns.AutoFit


'End With
End Sub

Hope this helps, don't forget to test it on a copy workbook first

Coops

 

[MajorK]

It Worked! 1 additional question, what need to be done to switch column and rows?

 

[MajorK]

It Worked! 1 additional question, what need to be done to switch column and rows?

 

[cooper645]

It Worked! 1 additional question, what need to be done to switch column and rows?

Not sure what you mean. Why do you need to switch columns and rows? from your first post this simply adds the update to the next empty column?