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:

[TABLE="width: 205"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Blatt1[/TD]
[/TR]
[TR]
[TD]ub deg[/TD]
[/TR]
[TR]
[TD]Fitting[/TD]
[/TR]
[TR]
[TD]Summary[/TD]
[/TR]
[TR]
[TD]P1_9wFVB[/TD]
[/TR]
[TR]
[TD]P2_9wFVB[/TD]
[/TR]
[TR]
[TD]P3_9wFVB[/TD]
[/TR]
[TR]
[TD]P4_9wFVB[/TD]
[/TR]
[TR]
[TD]P5_9wFVB[/TD]
[/TR]
[TR]
[TD]P6_9wFVB[/TD]
[/TR]
[TR]
[TD]P7_9wFVB[/TD]
[/TR]
[TR]
[TD]P8_9wFVB[/TD]
[/TR]
[TR]
[TD]P9_9wFVB [/TD]
[/TR]
[TR]
[TD]P10_9wFVB[/TD]
[/TR]
[TR]
[TD]P11_9wFVB[/TD]
[/TR]
[TR]
[TD]P12_9wFVB[/TD]
[/TR]
[TR]
[TD]P13_9wFVB[/TD]
[/TR]
[TR]
[TD]P14_9wFVB[/TD]
[/TR]
[TR]
[TD]P15_9wFVB[/TD]
[/TR]
[TR]
[TD]P18_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P19_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P20_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P21_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P22_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P23_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P24_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P25_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P26_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P27_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P28_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P29_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P30_15wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P39_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P40_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P41_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P42_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P43_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P44_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P45_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P46_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P47_66wB6CBAF1[/TD]
[/TR]
[TR]
[TD]P50_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P51_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P52_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P53_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P54_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P55_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P57_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P58_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P59_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P60_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P61_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P62_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P65_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P66_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P67_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P68_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]P69_9wFvB-MG132[/TD]
[/TR]
[TR]
[TD]ni-P33[/TD]
[/TR]
[TR]
[TD]ni-P34[/TD]
[/TR]
[TR]
[TD]ni-P35[/TD]
[/TR]
[TR]
[TD]ni-P36[/TD]
[/TR]
[TR]
[TD]mean autofluorescence[/TD]
[/TR]
</tbody>[/TABLE]