copy cells from all sheets to one main sheet

Hey,
I want to copy some cells from all sheets in a workbook into one master workbook. I wrote this code:

HTML Code:

---

Dim kopia_nazwa
Dim praw1
Dim praw3
Dim praw5
Dim praw10   

kopia_nazwa = Range("a1").Value
Worksheets("data").Range("a3").Value = kopia_nazwa

praw1 = Range("e6").Value
Worksheets("data").Range("b" & i).Value = praw1

praw3 = Range("f6").Value
Worksheets("data").Range("c" & i).Value = praw3

praw5 = Range("g6").Value
Worksheets("data").Range("d" & i).Value = praw5

praw10 = Range("h6").Value
Worksheets("data").Range("e" & i).Value = praw10

---

I found code on the internet that executes the given code for all sheets in a workbook:

HTML Code:

---

Sub Dosomething2()
 
Dim xSh As Worksheet   
Application.ScreenUpdating = False     
For Each xSh In Worksheets       
xSh.Select       
Call RunCode2   
Next     
Application.ScreenUpdating = True

End Sub

---

I thought of combining it with a for loop:

HTML Code:

---

Sub Dosomething2()
 
Dim xSh As Worksheet   
Application.ScreenUpdating = False   
For i = 3 To 168   
For Each xSh In Worksheets       
xSh.Select       
Call RunCode2   
Next   
Next i   
Application.ScreenUpdating = True

End Sub


Sub RunCode2()

Dim kopia_nazwa
Dim praw1
Dim praw3
Dim praw5
Dim praw10   

kopia_nazwa = Range("a1").Value
Worksheets("data").Range("a3").Value = kopia_nazwa

praw1 = Range("e6").Value
Worksheets("data").Range("b" & i).Value = praw1

praw3 = Range("f6").Value
Worksheets("data").Range("c" & i).Value = praw3

praw5 = Range("g6").Value
Worksheets("data").Range("d" & i).Value = praw5

praw10 = Range("h6").Value
Worksheets("data").Range("e" & i).Value = praw10

End Sub

---

Why doesn't this work, or how can I do it differently?

Just explain what you want to achieve.
Make sure to let us know if all sheets or only some, range address or just single cells etc etc
Where to paste, which column etc etc.
Are both workbooks open or does one workbook need to be opened?

Integrate all worksheets into one and your question vanishes as snow in the sun.

Never split similar data into several worksheets or files or even directories.
If you use Excel as a database, treat the data as a database.

I want to:
Copy to the "main" sheet copy data from all other sheets in the workbook.
The cells I want to copy are A1, E6, F6, G6, H6.
The cells I want to paste into the "main" worksheet are B & x, C & x, D & x, E & x, F & x. Where x is 1, 2, 3 ... n.


snb: The workbook I have was created by based on a macro I got, so it is done this way. Thank you for the information.

Quote:

---

Originally Posted by kared

snb: The workbook I have was created by based on a macro I got, so it is done this way.

---

Why not simply fix the macro that creates the workbook then?

Kared, your code as you have written it will fail.

Firstly, please dim your variables as a particular variant. Whilst you know what you want others who look at your code will not necessarily understand the variant type over time, so to help them out try to Dim the variants as a particular type.

Next, Lines 6 & 7 of your submitted code will result in the value of each sheet's Range ("A1").value, overwriting the value in cell Worksheets(Data). Range ("A3"). value because it doesn't increment beyond Range("A3"). I'm assuming that's not what you had intended. Did you notice that you did not define the value "I"? "I' should be the last row of the column you selected namely "A", "E", "F","G", "H", and then you add "1" to find the next blank row.

The smarter people here will probably define an array based on "A1", "E6", "F6","G6","H6" of each sheet other than Worksheets("Data"), and then transpose that array to the next blank row per sheet. I await their replies with some interest. Your code as you had written it would never have worked.

Maybe something like the below for starters:

Code:

---

Sub test()   
    Dim wsMain As Worksheet, ws As Worksheet, var As Variant
   
    Set wsMain = Sheets("Main")
    For Each ws In ThisWorkbook.Sheets
        With ws
            If .Name <> "Main" Then
                var = Array(.Range("A1"), .Range("E6"), .Range("F6"), .Range("G6"), .Range("H6"))
                wsMain.Range("B" & wsMain.Range("B" & Rows.Count).End(xlUp).Row + 1).Resize(, UBound(var) + 1) = var
            End If
        End With
    Next ws
End Sub

---

Loops through all worksheets in the workbook (omitting 'Main') takes the required range into an array, places that array in 'Main' starting at the next available row in column B of 'Main'.

Hope this helps

Georgiboy...... you are seriously impressing me. If Boris steps down are you available?

Quote:

---

Originally Posted by Aussiebear

Georgiboy...... you are seriously impressing me. If Boris steps down are you available?

---

Thanks Aussie,

I am afraid that job over here is only for fools (as you may see on the news), it seems that the new world order is to have the world run by maniacs & morons.

I couldn’t party every day like Boris can...

Thank you for your help! Will analyze this code to learn from it.

Aussiebear: Thank you for your advice.

georgiboy: Thank you for your help! Will analyze this code to learn from it.

Aussiebear: Thank you for your advice.

Re Post #9 (georgiboy)
Have not heard a truer statement like that for a long time. Many examples all over the world.
The only problems is, in the west anyway, who voted them in. What does that say about the intelligence of the voters?