Solved: Copy multiple Excel Sheets

[afoehrin]

Hello All!

Well, for example I have 50 Excel Sheets. I would like to make a code that copies whatever I want from the first sheet and then jumped to the next one.
My idea was:

For n = 1 To 50
Sheets(n).Select
Range("C4").Select
Next n

The problem is that it is not accepting me to put Sheets(n).Select.

Can someone please help me. Your help will be much apreciated!

Thank You!

AJPF

[Bob Phillips]

Not accepting ... in what way, what do you get?

[afoehrin]

Well, it says:
Error in time of execution '9':
Subscript out of the range

My VBA is in portuguese. I don't know if this is the correct translation!

Thanks!

AJPF

[Bob Phillips]

That suggest you don't have 50 sheets to process through.

[Norie]

You shouldn't need to select anything in the first place.

What is it you actually want to do?

In your title you seem to be indicating you want to copy sheets, but the posted code doesn't have any copying of anything in it.

[afoehrin]

I have 50 sheet tabs with information. I want to copy some cells from each sheet to another excel base.

ActiveWindow.ActivateNext
For n = 1 To 2
Sheets(n).Select
Rows("1:8").Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("plan1").Select
Range("A5").Select
ActiveSheet.Paste
Next n

When I run it, it only copies the cells of the 1 sheet (when n = 1). It stops when n = 2.

Thanks!
AJPF

[Bob Phillips]

[vba]

For n = 1 To 2
Sheets(n).Rows("1:8").Copy Sheets("plan1").Range("A5")
Next n
[/vba]

see if that is any better (but I suspect protected sheets or something)

[Norie]

Why are you copying and then pasting to the same location over and over again from different worksheets?

Surely all you'll end up with is the data from the last sheet?

[afoehrin]

Dear Norie,
Sorry for the misunderstanding.
That was only an example of what I want to do.
I'm using a rowcounter in order not to paste to the same location every time.

Here is the whole code:

ActiveWindow.ActivateNext
For n = 1 To 2
'Sheets(n).Select
'Rows("1:8").Select
'Selection.Copy
Sheets(n).Rows("1:8").Copy
ActiveWindow.ActivateNext
Sheets("plan1").Select
i = 1
Conta = 1
Do While Cells(i, 1) <> ""
i = i + 1
Loop
Conta = i
col = i
Cells(col, 1).Select
ActiveSheet.Paste
Next n

Any help will be much apreciated!

Thanks!

AJPF

[Norie]

Still not 100% sure what you want, especially with all that unneeded activating/selectin, but try this.
[vba]
For n = 1 To 2
Sheets(n).Rows("1:8").Copy Sheets("plan1").Range("A1").Offset((n - 1) * 8)
Next n[/vba]

PS Another confusing thing is that's it's not clear if you are working with more than one workbook.

[afoehrin]

Dear Norie and xld,

It worked! Thank you very much for your support!

Thanks!

AJPF

[Norie]

What worked?

[afoehrin]

The macro worked! It is running without errors.
Thank you!

AJPF