Using worksheet position and range.value-CodePudding

I've got a code that generates a workbook by copying and moving selected worksheets into a new workbook. The first page of this new workbook is a summary page. On this i want to pull data from the subsequent worksheets by using the range.value method. However can I use this when referencing the worksheet location for example

Dim wb As Workbook, wbAll As Workbook
Dim ws As Worksheet

Set wbAll = Workbooks.Add
On Error Resume Next
For t = 1 To 100         
    Set wb = Workbooks("Book" & t)
    For Each ws In wb.Sheets
        ws.Move after:=wbAll.Sheets(Sheets.Count)
    Next
Next
Workbooks("Book" & t).Activate
ActiveWorkbook.Sheets("Sheet1").Select

'compile worksheets into list
Dim wss As Worksheet
Dim x As Integer

On Error Resume Next
 
x = 17
 
Sheets("Sheet1").Range("c17:E46").ClearContents

 
For Each wss In ActiveWorkbook.Worksheets
    If wss.Name <> "Sheet1" Then
         Sheets("Sheet1").Cells(x, 3) = wss.Name
         x = x   1
    End If
Next wss

'COMPILE COSTS
ActiveWorkbook.Sheet1.Range("C17").Value = ActiveWorkbook.Worksheet(2).Range("Q118").Value
ActiveWorkbook.Sheet1.Range("C18").Value = ActiveWorkbook.Worksheet(3).Range("Q118").Value
.
.
ActiveWorkbook.Sheet1.Range("C45").Value = ActiveWorkbook.Worksheet(30).Range("Q118").Value
ActiveWorkbook.Sheet1.Range("C46").Value = ActiveWorkbook.Worksheet(31).Range("Q118").Value

'Compile WBS
ActiveWorkbook.Sheet1.Range("D17").Value = ActiveWorkbook.Worksheet(2).Range("D10").Value
ActiveWorkbook.Sheet1.Range("D18").Value = ActiveWorkbook.Worksheet(3).Range("D10").Value
.
.
ActiveWorkbook.Sheet1.Range("D45").Value = ActiveWorkbook.Worksheet(30).Range("D10").Value
ActiveWorkbook.Sheet1.Range("D46").Value = ActiveWorkbook.Worksheet(31).Range("D10").Value


'Week Number name
ActiveWorkbook.Sheet1.Range("C10").Value = ActiveWorkbook.Worksheet(2).Range("D4").Value
'Supplier Name
ActiveWorkbook.Sheet1.Range("C12").Value = ActiveWorkbook.Worksheet(2).Range("D5").Value

This however gives me an error message of object defined error

CodePudding user response：

This may help:

Sub Tester()
    
    Dim wb As Workbook, wbAll As Workbook
    Dim ws As Worksheet
    Dim wss As Worksheet
    Dim x As Integer, wsSummary, t As Long
    
    Set wbAll = Workbooks.Add
    
    For t = 1 To 100
        Set wb = Nothing
        On Error Resume Next    'ignore any error
        Set wb = Workbooks("Book" & t)
        On Error GoTo 0         'cancel OERN as soon as possible
        If Not wb Is Nothing Then
            For Each ws In wb.Sheets
                ws.Move after:=wbAll.Sheets(wbAll.Sheets.Count)
            Next
        End If
    Next
    'Workbooks("Book" & t).Activate   'not sure what this is for?
    'ActiveWorkbook.Sheets("Sheet1").Select
    
    'compile worksheets into list
    x = 17
    Set wsSummary = wbAll.Sheets("Sheet1")
    wsSummary.Range("C17:E46").ClearContents
    
    For Each wss In wbAll.Worksheets
        If wss.Name <> wsSummary.Name Then
             With wsSummary.Rows(x)
                .Cells(3).Value = wss.Name
                .Cells(4).Value = wss.Range("Q118").Value
                .Cells(5).Value = wss.Range("D10").Value
                'etc etc
             End With
             x = x   1
        End If
    Next wss

End Sub