Stubborn array wont change destination worksheet - HELP! (reposteddue to incorrect formatting)

[dial13]

I have tried everything but the array stubbornly refuses to populate
any sheet except the one the code belongs to:

Sub WontChangeWorksheetDestination()

Dim wks As Worksheet
Set wks = Sheets(3)

With wks

Set TheRange = Range(Cells(1, 1), Cells(10, 10))

TheRange.Value = MyArray

End With

End sub

I've tried everything, but because the code is behind a button on
sheet 1 the array populates sheet 1 and doesn't seem to want to
populate any toher sheet no mattwer hw I try to qualify it ... what am
I missing?

Any help gratefully accepted,

Kind regards, Mark

 

[Vacuum Sealed]

I have tried everything but the array stubbornly refuses to populate
any sheet except the one the code belongs to:

Sub WontChangeWorksheetDestination()

Dim wks As Worksheet
Set wks = Sheets(3)

With wks

Set TheRange = Range(Cells(1, 1), Cells(10, 10))

TheRange.Value = MyArray

End With

End sub

I've tried everything, but because the code is behind a button on
sheet 1 the array populates sheet 1 and doesn't seem to want to
populate any toher sheet no mattwer hw I try to qualify it ... what am
I missing?

Any help gratefully accepted,

Kind regards, Mark

Hi

Not sure about your use of MyArray as you have not declared it, unless
you have it Globally Declared somewhere else in your workbook.

Try

Dim wks As Worksheet
Dim TheRange as Range
Set wks = Sheets(3)
Set TheRange = wks.Range(Cells(1, 1), Cells(10, 10))
with TheRange
.Value = MyArray
End With
End Sub

HTH
Mick.

 

[Bob Flanagan]

You need to link the Cells() statements back to the tarket worksheet.
Do not use "wks.range". Instead do this:

range(wks.cells(1,1), wks.cells(10,10))

or

with wks
range(.cells(1,1), cells(10,10))
end with

Without the link back to wks, Cells() will refer to the active
worksheet only. And if you qualify Range with wks, that doesn't
change what Cells() is referring to

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel