Hi Everyone,
I have a code that will go and source data from a specific range all worksheets within the workbook.
I cannot seems to get it to ignore "0" values or empty cells. Tried a few things but keep getting "Next without For" errors. I am quite a novice to VBA so could really use some guidance here.
Here is my code. Basically want the myCell.value to not include "0" results
I have a code that will go and source data from a specific range all worksheets within the workbook.
I cannot seems to get it to ignore "0" values or empty cells. Tried a few things but keep getting "Next without For" errors. I am quite a novice to VBA so could really use some guidance here.
Here is my code. Basically want the myCell.value to not include "0" results
Code:
ub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Req 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
'Add a worksheet with the name "Requirements Gathering"
Set Basebook = ThisWorkbook
Set Req = Worksheets("Requirements Gathering")
'The links to the first sheet will start column 1
ColNum = 1
For Each Sh In Basebook.Worksheets
If Sh.Name <> Req.Name And Sh.Visible Then
RwNum = 11
ColNum = ColNum + 1
Columns("C:C").Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Get the values
Req.Cells(RwNum, ColNum).Value = Sh.Name
For Each myCell In Sh.Range("Q2,Q3,Q4")
RwNum = RwNum + 1
Req.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Req.Cells.NumberFormat = "General"
Next myCell
End If
Next Sh
Req.UsedRange.Columns.AutoFit
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub