range that spans two worksheets

[Darren]

Has anyone got any Idea how I can insert data into a range that spans
two worksheets with one command. At the moment I need to select each
worksheet and insert the data rather than have the data inserted
without selecting the worksheet.

I hope this makes sense.
Darren

 

[Charles Williams]

a Range object can only refer to a single worksheet, so I do not think you
can do this using VBA.

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[Tom Ogilvy]

That said, there is no reason to select a sheet to place values in it.

Sub Tester1()
For i = 1 To 2
Worksheets(i).Range("B9").Resize(1, 5) = Array("A", 1, "B", 2, "C")
Next
End Sub

--
Regards,
Tom Ogilvy

 

[Fernando Ortiz]

Hi Darren

Try this:

Select a few sheets holding ctrl + click in the sheets labels, after this,
you can insert data in tha both sheets. just only entry data in the
activesheet.

Regards,

Fernando Ortiz

 

[Gord Dibben]

Darren

Have you given any thought to "linking" cells in Sheet2 to Sheet1?

On Sheet2 in a cell enter =Sheet1!A1

Now, whenever you change A1 on Sheet1, Sheet2 cell will update.

Gord Dibben Excel MVP