MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to access many worksheets from one

  Asked By: Cesar    Date: Feb 04    Category: MS Office    Views: 856

I have a Worksheet called MASTER and many other worksheets like 101,
102, etc in the same Workbook.
MASTER is a synopsis of all others, compiled via a VBA macro.
Is there a way to access cells of the data worksheets from MASTER?
That is, without having to got to that sheet? (Like I do now).
My present method produces a lot of flashing screens - very
disconcerting to users.
Maybe a way to build a string for the cell references? or else ... ???

Any ideas?



4 Answers Found

Answer #1    Answered By: Viveka Fischer     Answered On: Feb 04

if your going sheet to sheet from vba and it's the flickering of the sheet
changes.. then you can use:

application.screenupdating = false

then when done set it to true.. and you won't see the flickering.

Answer #2    Answered By: Cadeo Nguyen     Answered On: Feb 04

when you want to get rid of the flashing screens, put in the next line at the
start of the macro:

Application.ScreenUpdating = False

and at the end:

Application.ScreenUpdating = True

This will freeze the screen and run the whole process at the background, without
updating its screen. Your macro will run faster as well.

Answer #3    Answered By: Lena Moore     Answered On: Feb 04

Don't use copy/paste/select/activate etc.

Access them properly from the code ...

Worksheets("101").Cells(27, 6).Value

or whatever.

Alternatively, turn screen updating off (it's an Application property).

Answer #4    Answered By: Kathleen Adams     Answered On: Feb 04

The use of range names and the Offset method can make it easier to refer to data
on other sheets programmatically:

ActiveWorkbook.Sheets("Widgets").Range("SalesData").Offset(1, 0).Value

Also, you can use the Vlookup function from VBA:

Data$ = Application.WorksheetFunction.VLookup("WhatToLookFor",
ThisWorkbook.Sheets("Widgets").Columns("A:D"), 2, False)

Didn't find what you were looking for? Find more on How to access many worksheets from one Or get search suggestion and latest updates.