Simple VBA operation

[nicksinthemi]

Hi

New to this forum & VBA. What I'm trying to do seems quite simple but I'd really appreciate some help:

I need to copy cell C11 from 529 sheets to another sheet in the same workbook called "Overview". I just need a list alongside the name of each sheet.

I have a range of cells in a different sheet ("Summary") that gives the names of all 529 sheets (C12:C540).

This is some kind of loop? I've scratched around for some examples but everyone seems to be dealing with unstable ranges. With this, I know every piece of info is in cell C11 and it is all in the same workbook.


Thanks in advance.

[Stargazer]

Hi,

I'd do it with a 'For'... Something along the lines of:-


'Code deleted because I'm an idiot and I did it wrong. See next couple of posts!

That is assuming that you are writing to Columns A and B in the Overview sheet each time.

If it is not A and B, then update the ranges accordingly and ensure that the RowToWrite Count number is relevant to the column in which this value is written to.

Regards,

Rob.

[Stargazer]

Ack... Hang on... I just figured I'd try to run my own code and it doesn't work... I've just learned the perils of typing code directly into a forum without using the VBE.

Hang fire and I'll tweak it. Unless it's given you the inspiration to get it working yourself.

Rob.

[nicksinthemi]

Thanks Rob!

I'm being told that 'for' is an unrecognised syntax. Is there anything I need to do to it before plugging it in?

[Stargazer]

Hiya,

Yep... I'm an idiot... I did it wrong. I've just tested the following chunk in a real workbook and this one works.


Sub WriteVals()

For WorksheetList = 12 To 529 Step 1

WbkSource = Sheets("Summary").Range("C" & WorksheetList).Value

RowToWrite = Sheets("Overview").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Overview").Range("A" & RowToWrite).Value = Sheets("Summary").Range("C" & WorksheetList).Value
Sheets("Overview").Range("B" & RowToWrite).Value = Sheets(WbkSource).Range("C11").Value

Next WorksheetList

End Sub

Hope this helps,

Rob.

[nicksinthemi]

Thanks again Rob,

Just one thing - do I need to define 'WorkSheetList' before hand in some way?

[Stargazer]

You may if you have OptionExplicit enabled. It's not really necessary though and I find it just gets in the way a lot of the time so I don't use it.

Also, I forgot to add before, that this Sub would work best if ran from the 'Summary' Worksheet. If you want to enable it from somewhere else, put the code in the 'Summary' class/module and then call it from the other location.

[nicksinthemi]

Weird. It just keeps telling me there's a compile error (variable not defined) when it gets to WorkSheetList

[Stargazer]

And Option explicit is definitely not present? If it is there then it will complain because it forces you to declare something before you define it and in this case it simply isn't necessary.

Check at the very top of the class/module (I really ought to work out the correct name for these things) and if it says "Option Explicit" there, stick an apostrophe in front of it and then give her a whirl.

I'd bet 50p that's whats causing the issue.

[nicksinthemi]

Fantastic stuff, Rob!!! That's perfect!

[Aussiebear]

You may if you have OptionExplicit enabled. It's not really necessary though and I find it just gets in the way a lot of the time so I don't use it.

Its a recommended option when you're learning.....

[Aflatoon]

Its a recommended option when you're learning.....

... and since we never stop learning, you should always use it.