retrieving data from a worksheet that does not exist yet

[kungsleden]

Hi,
I did find a thread similar to my question but could not resolve my issue.
This is about biological experiments.
I have a workbook with a number of sheets in it. When performing a certain experiment, I use a piece of equipment that will create one (or several) new worksheet(s) in the open workbook. Each of these new worksheets will contain results in A1:D96. I want to have a formula in an existing worksheet that retrieves those results in cell C2 (f.ex.). Currently, I simply copy/paste the results from the new worksheet(s). This is fine but I would like to automate that step.
If a macro is required, that is ok.

 

[mrshl9898]

A macro will be essential.

Do you want A1to D96 in A1 to D96 and then E1 to H96 etc? Or do they all go in C2 as a string?

What are all the sheet names BEFORE the new ones are created? What's the name of the sheet you want the data in?

What happens to the sheets after they are added and the data is extracted?

 

[kungsleden]

All good questions. I am so used to the process I missed them.
A1:D96 of the first new sheet (Magellan1) goes to C2:F97 of an existing sheet (Sheet2 f.ex.).
A1:D96 of the next new sheet (Magellan2) goes to C98:F183 of Sheet 2, and so on.
Do you need the names of all sheets?
The new sheets should not be removed after data extraction.

 

[mrshl9898]

Still need to know the other sheet names, alternatively, are all new sheets called "Magellan" followed by a number?

 

[mrshl9898]

Something like:

Sub WorksheetLoop2()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Long
Dim FirstPass As Long

Set ws2 = Sheets("f.ex.")
FirstPass = 1

    For Each ws In Worksheets
        If ws.Name Like "Magellan*" And FirstPass = 0 Then
            LastRow = ws2.Cells(ws2.Rows.Count, "C").End(xlUp).Row
            ws.Range("A1:D96").Copy ws2.Range("C" & LastRow + 1)
        ElseIf ws.Name Like "Magellan*" And FirstPass = 1 Then
            ws.Range("A1:D96").Copy ws2.Range("C2")
            FirstPass = 0
        End If
    Next

End Sub

 

[kungsleden]

Thanks for the suggestion.
I have a working formula solution. It is a IF(ISERROR(INDIRECT(Location),"",INDIRECT(Location))) formula.