Copy paste values to summary sheet

[Va Leria]

Dear Community,

I am very new to excel and in urgent need of your help because I have a huge set of Data to analyze.

I have a file with a lot of worksheets (>50) that have all their content arranged in the same way. However, they are named differently (not Sheet1, Sheet2 but e.g. P1_F, P2_F, P3_control, P4_W and so on)


First, I would like to paste the same cell from a defined group of sheets into a defined range of cells on the summary sheet (eg. paste values of A1 from each sheet vertically into a column on the summary sheet.)

Additionally, would also like to paste ranges (i.e., columns) of the same cells of multiple sheets into adjacent columns of the summary sheet, and if possible, have the name of the corresponding worksheet as a header of each column on the summary sheet.

Is there a fast and efficient way to do this?

I tried to do it with a formula like ='Sheet1':'Sheet50'!A1 or 'Sheet1':'Sheet50'!A1:A50 but this obviously doesn't work.

I am working with excel Mac 2016 and therefore can't use Kutools.

Maybe there is a solution with a Macro or VBA code? Unfortunately, I have no experience whatsoever with these things.

I am kind of desperate and would be extremely grateful for any help from you experts!

Best wishes from Germany,
Va Leria

 

[My Aswer Is This]

Glad to see you here:
You said:
First, I would like to paste the same cell from a defined group of sheets into a defined range of cells on the summary sheet (eg. paste values of A1 from each sheet vertically into a column on the summary sheet.)

Not knowing what the defined group of sheets are.
Try this to accomplish your first request which was:
First, I would like to paste the same cell from a defined group of sheets into a defined range of cells on the summary sheet (eg. paste values of A1 from each sheet vertically into a column on the summary sheet.)
We always need specific details like what column. I used Column "A"



Script will copy Range("A1") from all sheets in your workbook into column "A" of sheet named "Summary"

I would need more specific details like column numbers sheet names etc.
To provide code for the other parts of your request.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
For i = 2 To Sheets.Count
    Sheets(i).Range("A1").Copy Sheets("Summary").Cells(i, 1)
Next
Application.ScreenUpdating = True
End Sub

 

[Va Leria]

Glad to see you here:
You said:
First, I would like to paste the same cell from a defined group of sheets into a defined range of cells on the summary sheet (eg. paste values of A1 from each sheet vertically into a column on the summary sheet.)

Not knowing what the defined group of sheets are.
Try this to accomplish your first request which was:
First, I would like to paste the same cell from a defined group of sheets into a defined range of cells on the summary sheet (eg. paste values of A1 from each sheet vertically into a column on the summary sheet.)
We always need specific details like what column. I used Column "A"



Script will copy Range("A1") from all sheets in your workbook into column "A" of sheet named "Summary"

I would need more specific details like column numbers sheet names etc.
To provide code for the other parts of your request.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
For i = 2 To Sheets.Count
    Sheets(i).Range("A1").Copy Sheets("Summary").Cells(i, 1)
Next
Application.ScreenUpdating = True
End Sub

Thank you very much for your reply!
the names of my worksheet look like this:

(there are some more sheets).

Sheets with the same suffix belong to one experimental group and I would like to copy the range "U6" from each experimental group into a separate column on the summary sheet with (U6 from "Tabelle 10" to "Tabelle 19" into column A, U6 from "Tabelle19" to "Tabelle30" into column B of summary sheet and so on.

I tried it with your code and because the reference cell U6 contains the formula AVERAGE, the term REFERENCE! instead of the value appears in the cells of the summary sheet. How doI solve this problem? Can I copy the values instead of a formula?

Additionally, I would like to copy cells S5:S31 from every single worksheets onto a summary sheet (I could call it Summary2, for instance) into adjacent columns (B,C, and so on) and ideally have the name of the worksheet where the values are from as the respective header for the column.

I hope this is more or less understandable. Many thanks in advance and all the best,

Va Leria

 

[Va Leria]

Sorry, I didn't realize the image is not displayed in the thread.

Here is a list of all my worksheet names, if it helps:

Blatt1

ub deg

Fitting

Summary

P1_9wFVB

P2_9wFVB

P3_9wFVB

P4_9wFVB

P5_9wFVB

P6_9wFVB

P7_9wFVB

P8_9wFVB

P9_9wFVB

P10_9wFVB

P11_9wFVB

P12_9wFVB

P13_9wFVB

P14_9wFVB

P15_9wFVB

P18_15wB6CBAF1

P19_15wB6CBAF1

P20_15wB6CBAF1

P21_15wB6CBAF1

P22_15wB6CBAF1

P23_15wB6CBAF1

P24_15wB6CBAF1

P25_15wB6CBAF1

P26_15wB6CBAF1

P27_15wB6CBAF1

P28_15wB6CBAF1

P29_15wB6CBAF1

P30_15wB6CBAF1

P39_66wB6CBAF1

P40_66wB6CBAF1

P41_66wB6CBAF1

P42_66wB6CBAF1

P43_66wB6CBAF1

P44_66wB6CBAF1

P45_66wB6CBAF1

P46_66wB6CBAF1

P47_66wB6CBAF1

P50_9wFvB-MG132

P51_9wFvB-MG132

P52_9wFvB-MG132

P53_9wFvB-MG132

P54_9wFvB-MG132

P55_9wFvB-MG132

P57_9wFvB-MG132

P58_9wFvB-MG132

P59_9wFvB-MG132

P60_9wFvB-MG132

P61_9wFvB-MG132

P62_9wFvB-MG132

P65_9wFvB-MG132

P66_9wFvB-MG132

P67_9wFvB-MG132

P68_9wFvB-MG132

P69_9wFvB-MG132

ni-P33

ni-P34

ni-P35

ni-P36

mean autofluorescence

<colgroup><col></colgroup><tbody>
</tbody>