help with VBA for macro please?

[tobyo]

especially the part with the range. I'm trying to modify it so that it works but my VBA knowledge is very limited. so, hoping someone can translate what this VBA code means. I don't understand the format of this range in the middle of this macro and I see that the notation says it has to be changed:

For Each myCell In sh.Range("A2,B2,F28,F29")

Here's the entire VBA code behind this macro:

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("A2,B2,F28,F29") '<--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

 

[JLGWhiz]

For Each myCell In sh.Range("A2,B2,F28,F29")

Whoever wrote the macro was telling the user that if they want the macro to apply to a different range other than those four cells, that this is the place to make the change. If you wanted, for example, cells B5:F5 Then

For Each myCell In sh.Range("B5:F5")

So if you made that change in the macro, then those six cells would be the ones used in the formula that is added to the new sheet.
It does not mean that you have to change it, just that if you need to change it, then that is the place to do it.

 

[tobyo]

okay that helps but are you also able to tell me what the macro does? I may be able to find the person that created it to ask but not sure so am starting from scratch. It's the first of 6 modules that are supposed to take a file with 45 similarly formatted worksheets and puts them all into their own files. I'm not understanding what this one does so looking for help.

thanks!

edited: here's the description within the file for what it does "pulls data from same cells on all worksheets in workbook into summary worksheet". so, is this macro taking all data on all 45 tabs and putting it into one tab then?

 

[JLGWhiz]

Read the comments which are preceded with an apostrophe (') for line by line explanation of the code.

Sub Summary_All_Worksheets_With_Formulas() 'Title line
Dim sh As Worksheet 'All Dim statements are declarations of variables
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
    With Application 'Addresses Excel to set internal functions
        .Calculation = xlCalculationManual 'prevent automatic calculation keyed by changes
        .ScreenUpdating = False 'helps control flicker and flash of screen while code runs
    End With
'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False 'Prevents messages from showing on screen
On Error Resume Next 'If there is no summary sheet code continues instead of pausing
ThisWorkbook.Worksheets("Summary-Sheet").Delete 'Deketes the sheet if there is one
On Error GoTo 0 'Resets the error flags
Application.DisplayAlerts = True 'Turns messaging back on
'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook 'Sets ActiveWorkbook to a variable; could just as easily use ThisWorkbook
Set Newsh = Basebook.Worksheets.Add 'Adds a worksheet with variable Newsh.
Newsh.Name = "Summary-Sheet" 'Names the new worksheet
'The links to the first sheet will start in row 2
RwNum = 1 'initializes row number variable
    'Begins  a loop to address each worksheet
    For Each sh In Basebook.Worksheets
        If sh.Name <> Newsh.Name And sh.Visible Then 'Avoids working the destination sheet
            ColNum = 1 'initializes column number variable
            RwNum = RwNum + 1 'increment row number variable value by 1
            'Copy the sheet name in the A column
            Newsh.Cells(RwNum, 1).Value = sh.Name
            'Start an internal loop to work the specified range
            For Each myCell In sh.Range("A2,B2,F28,F29") '<--Change the range
                ColNum = ColNum + 1 'increment column number variable value by 1
                'Insert formula into cell in range
                Newsh.Cells(RwNum, ColNum).Formula = _
                "='" & sh.Name & "'!" & myCell.Address(False, False)
            Next myCell 'Reiterate inner loop until all cells in range are worked
        End If
    Next sh 'Reiterate outer loop until all sheets are worked
Newsh.UsedRange.Columns.AutoFit 'resize columns to compensate for any overrun text
    With Application 'Address Excek again
        .Calculation = xlCalculationAutomatic 'Reinstate automatic calculation
        .ScreenUpdating = True 'reinstat screem updating
    End With
End Sub