How can I pull data from identical cells across multiple worksheets

[ca11059]

I tried to record a macro, but I'm sure I need a bit more help. Not a VB programmer/expert at all..
Anyway, I need the data from the following cells from ALL worksheets in a workbook (except one: Report worksheet). I also would like the data to be entered in a new row for each sheet (not row 41 like my Macro does). Finally, I want column A to create a hyperlink where the SubAddress maps back to the appropriate worksheet, the A1 cell reference below is fine.

Please help.. Thanks.


Sub GrabData()
'
' GrabData Macro
'
'
ActiveWindow.SmallScroll Down:=9
Range("A41").Select
ActiveCell.FormulaR1C1 = "='239855-372'!R[-39]C[4]"
Range("B41").Select
ActiveCell.FormulaR1C1 = "='239855-372'!R[-39]C[6]"
Range("C41").Select
ActiveCell.FormulaR1C1 = "='239855-372'!R[-39]C[-1]"
Range("D41").Select
ActiveCell.FormulaR1C1 = "='239855-372'!R[-33]C[-2]"
Range("A41").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"GL%20Assessment%20Summary.xlsx", SubAddress:="'239855-372'!A1"
End Sub

 

[shg]

Welcome to the board.

One way:

Sub GrabData()
    Dim wksRep      As Worksheet
    Dim wks         As Worksheet
    Dim rOut        As Range
 
    Set wksRep = Worksheets("Report")
    Set rOut = wksRep.Cells(wksRep.Rows.Count, "A").End(xlUp)
 
    For Each wks In Worksheets
        If Not wks Is wksRep Then
            Set rOut = rOut.Offset(1)
            rOut.Range("A1:D1").FormulaR1C1 = Array("='239855-372'!R[-39]C[4]", _
                                                    "='239855-372'!R[-39]C[6]", _
                                                    "='239855-372'!R[-39]C[-1]", _
                                                    "='239855-372'!R[-33]C[-2]", _
                                                    "='239855-372'!R[-39]C[4]")
            ActiveSheet.Hyperlinks.Add _
                    Anchor:=rOut, _
                    Address:="GL%20Assessment%20Summary.xlsx", SubAddress:=""
        End If
    Next wks
End Sub

 

[shg]

Looking at that again,

Sub GrabData()
    Dim wksRep      As Worksheet
    Dim wks         As Worksheet
    Dim rOut        As Range
    Dim sWks        As String
 
    Set wksRep = Worksheets("Report")
    Set rOut = wksRep.Cells(wksRep.Rows.Count, "A").End(xlUp)
 
    For Each wks In Worksheets
        If Not wks Is wksRep Then
            sWks = "='" & wks.Name & "'!"
            Set rOut = rOut.Offset(1)
            rOut.Range("A1:D1").FormulaR1C1 = Array(sWks & "R[-39]C[4]", _
                                                    sWks & "R[-39]C[6]", _
                                                    sWks & "R[-39]C[-1]", _
                                                    sWks & "R[-33]C[-2]")
            ActiveSheet.Hyperlinks.Add _
                    Anchor:=rOut, _
                    Address:="GL%20Assessment%20Summary.xlsx", _
                    SubAddress:=sWks & "A1"
        End If
    Next wks
End Sub

 

[ca11059]

Thank you so much for the help, and Thanks for the code. I am having a problem getting it to populate the worksheet with the data, after stepping through it, I notice that the value for 'rOut' has a value of 0 (zero), even after running through the If stmt for several sheets. However, the Row value for 'rOut' has a value of 2485 and continues to increment, even after stopping and starting the macro - do I need to set that to 1 or 2 at the beginning? Either way, no data is populated on the row indicated.

There seem to be some really high values for some other settings under rOut (tho I'm not sure what they mean or if they matter):

ReadingOrder = -5002
Top = 37260

The other variables look correct as it runs and no errors. Just need the data to populate in the current sheet..

Thanks again.

 

[ca11059]

OK, I think I see what happened, everything populated on the Report sheet (thanks for taking that into account!).. So you it looks like you appended all data to the existing sheet. Correct me if I'm wrong there..

All the hyper links are all working correctly, just need to check why the data is all zeroes.. I'll let ya know if I need anymore help.

Thanks again, really - this really helps me out.

 

[shg]

You're welcome, good luck.