The VBA code below: reference same cell from multiple worksheets.However, How can I modify the code reference to copy "50 worksheets", that has values in both "F2 & G2"and paste the same values into a new workbook as a list on "C2 & D2" downwards"
Here is the code to be modify:
Sub AutoFillSheetNames()
'Update 20131202
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub