Simple For Each Excel loop doesnt work 2

[ljsmith91]

I have an Excel workbook with several sheets and I am trying to build a macro that will grab selected info from each sheet and write a summary sheet. I am new to this and built a simple routine to see if I could grab a specific cell and it doesn't work. The output from message box tells me that I am paging through each sheet ok but the "B4" cell doesnt get printed. Instead I get the "B4" Cell of the active sheet only. What am I doing wrong ? Here is the code. Thanks for any help.

Sub Test()
Dim Item As Worksheet
For Each Item In ActiveWorkbook.Worksheets
Range("B4").Select
MsgBox "Sheet= " & Item.Name & " Cell= " & SelectedRange
Next Item
End Sub

 

[PHV]

Sub Test()
Dim Item As Worksheet
For Each Item In ActiveWorkbook.Worksheets
MsgBox "Sheet= " & Item.Name & " Cell= " & Item.Range("B4")
Next Item
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

AS PHV has demonstrated, there is no need to SELECT a sheet or range as long as it is properly referenced.

In fact, selecting sheets & ranges slows down your process unnecessarily.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[ljsmith91]

Thanks so much. I have much to learn and this will get me on my way.

 

[MarjiS]

Skip,

AS PHV has demonstrated, there is no need to SELECT a sheet or range as long as it is properly referenced.

I'm hitting brick walls trying to get Excel to act like a database (because the boss said so). I know I'm not referencing worksheets, ranges, and cells properly and it's killing me. Where do I find this info?

Thanks.

Marji S

 

[ljsmith91]

I would love to know the same. I also struggle with finding useful doc when it comes to VBA.

 

[SkipVought]

Check out HELP in VBA for object. You will find the Excel Object model. Look at Workbooks & Worksheets to begin with. Also look at the Range object.

Then look at HELP in Excel for list and Guidelines for creating a list on a worksheet. This will be the starting point for using Excel as a database.

I like John Walkenback reference books on Excel VBA. There are other good references. Often this depends on your own learning style preference.

Use the macro recorder. Learn from the generated code. Modify the code to customize to your needs. Read other Tek-Tips posts. Experiment. Try stuff. Ask questions.

Look at the forum FAQs. They often have code segments.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[MarjiS]

Thanks, Skip. I'll start there.
Marji

 

[xlbo]

Would just like to back Skip up on this. IMHO, the OBJECT MODEL (available via F2 in the VBE) is the single most useful item as far as help goes. It shows you all the methods, properties and functions associated with each object - allowing you to better understand what can and cannot be done. You can jump straight to the help files for each object / method etc via the right click menu

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question