copy cells from all sheets to one main sheet

[kared]

Hey,
I want to copy some cells from all sheets in a workbook into one master workbook. I wrote this 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:



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:



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?

[jolivanes]

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?

[snb]

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.

[kared]

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.

[Aussiebear]

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?

[Aussiebear]

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.

[georgiboy]

Maybe something like the below for starters:

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

[Aussiebear]

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

[georgiboy]

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...

[kared]

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

Aussiebear: Thank you for your advice.

[kared]

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

Aussiebear: Thank you for your advice.

[jolivanes]

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?