PDA

View Full Version : Solved: Copy multiple Excel Sheets



afoehrin
09-20-2007, 04:25 AM
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
09-20-2007, 05:03 AM
Not accepting ... in what way, what do you get?

afoehrin
09-20-2007, 05:39 AM
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
09-20-2007, 06:31 AM
That suggest you don't have 50 sheets to process through.

Norie
09-20-2007, 08:08 AM
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.:dunno

afoehrin
09-20-2007, 09:24 AM
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
09-20-2007, 09:32 AM
For n = 1 To 2
Sheets(n).Rows("1:8").Copy Sheets("plan1").Range("A5")
Next n


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

Norie
09-20-2007, 09:38 AM
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
09-20-2007, 09:50 AM
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
09-20-2007, 09:56 AM
Still not 100% sure what you want, especially with all that unneeded activating/selectin, but try this.

For n = 1 To 2
Sheets(n).Rows("1:8").Copy Sheets("plan1").Range("A1").Offset((n - 1) * 8)
Next n

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

afoehrin
09-20-2007, 10:14 AM
Dear Norie and xld,

It worked! Thank you very much for your support!

Thanks!

AJPF

Norie
09-20-2007, 10:40 AM
What worked?

afoehrin
09-21-2007, 07:27 AM
The macro worked! It is running without errors.
Thank you!

AJPF