choosing a particular sheet

[izzyq]

What is the syntax to pick a specifcally named sheet to paste to instead of the "active sheet". The sheet name would be "2002". What I have so far will copy info from a database and paste it into the active sheet:

Option Explicit


Sub GetData2()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim xlWs As Object


Dim recArray As Variant

Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer

' Set the string to the path of your database
strDB = "\\\\Quartz\\Common\\Comptrol\\Corp_Rep\\MONTHEND\\2002\\Monthly Stewardship\\Stewardship Master Files\\Costbudget.mdb"

' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"

' Open recordset based on Cost and Budget query
rst.Open "Select * from qryCOSTBudget 2002", cnt

' Set the sheet where data will be copied
Set xlWs = "2002"
Help Me Obi Wan Kenobi, You're My Only Hope.

 

[bryanbayfield]

It should be:

Set xlWs = Sheets("2002")

You might also want to set a range object to use as the upper left cell of the area to which you want to paste, unless you want that to be the active cell on 2002.

Set xlRng = Range("cell_ref")[code]

[code]xlWs.Select
xlRng.paste

If after doing this you want to return to the sheet that was active before the code executed you would have to set eg:

act_sht = ActiveSheet

before the line

xlWs.Select

after the paste operation you could then have teh following code

act_sht.Select
set act_sht = Nothing

Although since you have used automation you might have to enhance the code shown, ie I don't know which app you're executing this from. I've assumed Excel. SuperBry!