retrieving data from a worksheet that does not exist yet

kungsleden

New Member
Joined
Apr 12, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
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

New Member
Joined
Apr 12, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Still need to know the other sheet names, alternatively, are all new sheets called "Magellan" followed by a number?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
Something like:

VBA Code:
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

New Member
Joined
Apr 12, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks for the suggestion.
I have a working formula solution. It is a IF(ISERROR(INDIRECT(Location),"",INDIRECT(Location))) formula.
 
Solution

Forum statistics

Threads
1,147,743
Messages
5,742,935
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top