VBA - How to Write to Specific Worksheets
 

I am using VBA to enter data into cells in Excel 2000. I have not been able
to figure out how to get the relevant data to the first worksheet, then
switch to the second worksheet and enter that relevant data.

Below is simplified code sample of what I am trying to do:


Dim objExcel As New Excel.Application
Set objExcel = New Excel.Application
objExcel.SheetsInNewWorkbook = 2
Dim objWBK As Excel.Workbook
Set objWBK = objExcel.Workbooks.Add
Dim objWKS As Excel.Worksheet
Set objWKS = objWBK.Worksheets.Add
objWBK.Worksheets(1).Name = "By INVOICE"
objWBK.Worksheets(2).Name = "By PROD LINE"

objWBK.SaveAs "BatchPayment_" & Format(Date, "mmddyyyy")

' OK SO FAR

' THIS CORRECTLY WRITES THE DATA TO THE FIRST WORKSHEET

objWKS.Cells(1, 1).Value = "Company XXX Inc."
objWKS.Cells(2, 1).Value = "Wire Detail by Invoice Number"
objWKS.Cells(3, 1).Value = "Transfer Date: " & Format(Date, "dd-mmm-yyyy")

' THIS IS SUPPOSED TO WRITE TO THE SECOND WORKSHEET
' BUT IT IS OVERWRITING THE INFO ON THE FIRST SHEET

' objWBK.Worksheets(2).Activate

objWKS.Cells(1, 1).Value = "COmpany XXX Inc. Page 2"
objWKS.Cells(2, 1).Value = "Wire Detail by Product Group"
objWKS.Cells(3, 1).Value = "Transfer Date: " & Format(Date, "dd-mmm-yyyy")

etc.

How can I get write the 2nd block to the 2nd sheet?

Many thanks,
Mike Thomas

VBA - How to Write to Specific Worksheets
 

Why don't you just call that specific sheet:

Worksheets("By INVOICE").cells(1,1).Value = "COmpany XXX Inc. Page 2"

If the worksheets are all in the same workbook you don't even have t
define them. You can just call them like above.

Hope this helps

--
Message posted from http://www.ExcelForum.com