Hello I copied the macro "Create a summary worksheet from all workbooks"
from the Ron De Bruin site. Macro works (of course) except it does not add a
link to the worksheet.
So I'm obviously doing something wrong . below is the section of the macro
that is not working for me but I do not know how to corcect it. thanks in
advance
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("A1") ' ("A1,D5:E5,Z10")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

After I run the macro my summary looks like this - columns A has the names
of the sheets in column B there is a 0 and no link to the sheet
2008 Complete 0
2008 Product Catalog Price List 0
Consumable Parts Price List 0
US Instrument Price List 0
CD Instrument Price List 0
FLEXChip airport 0
International ACity airport 0
International ACity LIMS&AE airport 0
International ACity GxP airport 0
International C airport 0
International Q airport 0
International TCity airport 0
International TCity GxP airport 0
International X airport 0
International XCity airport 0
International XCity Plus Pkg airport 0
International 2000 airport 0
International 3000 airport 0
International 3000 GxP airport 0

There's nothing in this code that would convert the formulas to values.

Is there anything in the code you didn't share that does this?

Do you have any event macro that converts formulas to values--maybe even
something that changes case that doesn't look to see if it's processing a cell
with a formula?

Wanna Learn wrote:

Hello I copied the macro "Create a summary worksheet from all workbooks"
from the Ron De Bruin site. Macro works (of course) except it does not add a
link to the worksheet.
So I'm obviously doing something wrong . below is the section of the macro
that is not working for me but I do not know how to corcect it. thanks in
advance
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("A1") ' ("A1,D5:E5,Z10")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

After I run the macro my summary looks like this - columns A has the names
of the sheets in column B there is a 0 and no link to the sheet
2008 Complete 0
2008 Product Catalog Price List 0
Consumable Parts Price List 0
US Instrument Price List 0
CD Instrument Price List 0
FLEXChip airport 0
International ACity airport 0
International ACity LIMS&AE airport 0
International ACity GxP airport 0
International C airport 0
International Q airport 0
International TCity airport 0
International TCity GxP airport 0
International X airport 0
International XCity airport 0
International XCity Plus Pkg airport 0
International 2000 airport 0
International 3000 airport 0
International 3000 GxP airport 0




--

Dave Peterson

Thanks Dave
below is the entire code . When I run the macro I get a new Summary sheet
with all the names of the worksheets in column A and in column B I get the
number 0 and the formula in column B is =2008 Complete'!A1 Again thanks

Sub Summary_All_Worksheets_With_Formulas()
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

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'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("A1") ' ("A1,D5:E5,Z10")
'<--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



"Dave Peterson" wrote:

There's nothing in this code that would convert the formulas to values.

Is there anything in the code you didn't share that does this?

Do you have any event macro that converts formulas to values--maybe even
something that changes case that doesn't look to see if it's processing a cell
with a formula?

Wanna Learn wrote:

Hello I copied the macro "Create a summary worksheet from all workbooks"
from the Ron De Bruin site. Macro works (of course) except it does not add a
link to the worksheet.
So I'm obviously doing something wrong . below is the section of the macro
that is not working for me but I do not know how to corcect it. thanks in
advance
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("A1") ' ("A1,D5:E5,Z10")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

After I run the macro my summary looks like this - columns A has the names
of the sheets in column B there is a 0 and no link to the sheet
2008 Complete 0
2008 Product Catalog Price List 0
Consumable Parts Price List 0
US Instrument Price List 0
CD Instrument Price List 0
FLEXChip airport 0
International ACity airport 0
International ACity LIMS&AE airport 0
International ACity GxP airport 0
International C airport 0
International Q airport 0
International TCity airport 0
International TCity GxP airport 0
International X airport 0
International XCity airport 0
International XCity Plus Pkg airport 0
International 2000 airport 0
International 3000 airport 0
International 3000 GxP airport 0




--

Dave Peterson

That formula:
='2008 complete'!a1
is a link to the other worksheet.

If the problem is that '2008 complete'!a1 is empty and you don't want to show 0
(you want to see ""), you can change the formula:

=if('2008 complete'!a1="","",'2008 complete'!a1)

In the code:

NewSh.Cells(RwNum, ColNum).Formula = _
"=if('" & Sh.Name & "'!" & myCell.Address(False, False) & "="""",""""," _
& "'" & Sh.Name & "'!" & myCell.Address(False, False) & ")"

Wanna Learn wrote:

Thanks Dave
below is the entire code . When I run the macro I get a new Summary sheet
with all the names of the worksheets in column A and in column B I get the
number 0 and the formula in column B is =2008 Complete'!A1 Again thanks

Sub Summary_All_Worksheets_With_Formulas()
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

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'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("A1") ' ("A1,D5:E5,Z10")
'<--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

"Dave Peterson" wrote:

There's nothing in this code that would convert the formulas to values.

Is there anything in the code you didn't share that does this?

Do you have any event macro that converts formulas to values--maybe even
something that changes case that doesn't look to see if it's processing a cell
with a formula?

Wanna Learn wrote:

Hello I copied the macro "Create a summary worksheet from all workbooks"
from the Ron De Bruin site. Macro works (of course) except it does not add a
link to the worksheet.
So I'm obviously doing something wrong . below is the section of the macro
that is not working for me but I do not know how to corcect it. thanks in
advance
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("A1") ' ("A1,D5:E5,Z10")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

After I run the macro my summary looks like this - columns A has the names
of the sheets in column B there is a 0 and no link to the sheet
2008 Complete 0
2008 Product Catalog Price List 0
Consumable Parts Price List 0
US Instrument Price List 0
CD Instrument Price List 0
FLEXChip airport 0
International ACity airport 0
International ACity LIMS&AE airport 0
International ACity GxP airport 0
International C airport 0
International Q airport 0
International TCity airport 0
International TCity GxP airport 0
International X airport 0
International XCity airport 0
International XCity Plus Pkg airport 0
International 2000 airport 0
International 3000 airport 0
International 3000 GxP airport 0




--

Dave Peterson


--

Dave Peterson

Dave Thank you , Thank You!

"Dave Peterson" wrote:

That formula:
='2008 complete'!a1
is a link to the other worksheet.

If the problem is that '2008 complete'!a1 is empty and you don't want to show 0
(you want to see ""), you can change the formula:

=if('2008 complete'!a1="","",'2008 complete'!a1)

In the code:

NewSh.Cells(RwNum, ColNum).Formula = _
"=if('" & Sh.Name & "'!" & myCell.Address(False, False) & "="""",""""," _
& "'" & Sh.Name & "'!" & myCell.Address(False, False) & ")"

Wanna Learn wrote:

Thanks Dave
below is the entire code . When I run the macro I get a new Summary sheet
with all the names of the worksheets in column A and in column B I get the
number 0 and the formula in column B is =2008 Complete'!A1 Again thanks

Sub Summary_All_Worksheets_With_Formulas()
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

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'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("A1") ' ("A1,D5:E5,Z10")
'<--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

"Dave Peterson" wrote:

There's nothing in this code that would convert the formulas to values.

Is there anything in the code you didn't share that does this?

Do you have any event macro that converts formulas to values--maybe even
something that changes case that doesn't look to see if it's processing a cell
with a formula?

Wanna Learn wrote:

Hello I copied the macro "Create a summary worksheet from all workbooks"
from the Ron De Bruin site. Macro works (of course) except it does not add a
link to the worksheet.
So I'm obviously doing something wrong . below is the section of the macro
that is not working for me but I do not know how to corcect it. thanks in
advance
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("A1") ' ("A1,D5:E5,Z10")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

After I run the macro my summary looks like this - columns A has the names
of the sheets in column B there is a 0 and no link to the sheet
2008 Complete 0
2008 Product Catalog Price List 0
Consumable Parts Price List 0
US Instrument Price List 0
CD Instrument Price List 0
FLEXChip airport 0
International ACity airport 0
International ACity LIMS&AE airport 0
International ACity GxP airport 0
International C airport 0
International Q airport 0
International TCity airport 0
International TCity GxP airport 0
International X airport 0
International XCity airport 0
International XCity Plus Pkg airport 0
International 2000 airport 0
International 3000 airport 0
International 3000 GxP airport 0




--

Dave Peterson


--

Dave Peterson