Not sure what you're stating is clear. If you want to specify active sheet to the code for the open workbook, your VBA code would be something like:
-----------------------------------------------------------
Private Sub MySub()
Dim oBook As Workbook, oSomeSheet As Worksheet 'Plus whatever else you declare
'***Existing Code, including 'Set oBook = <Workbook you're working with>'***
'Below sample assumes the active sheet is within the book you're working in
Set oBook = ThisWorkbook
Set oSomeSheet = oBook.ActiveSheet
'Now oSomeSheet is an object representation of the sheet you want to work on
End Sub
-----------------------------------------------------------
IF instead you need to iterate through all sheets within a workbook, you could do something like this:
-----------------------------------------------------------
'Assumes oBook is set to the workbook being worked with.
Dim oBook As Workbook, oSheetOn As Worksheet, iCol As Long, iRow As Long
set oBook = ThisWorkbook
For Each oSheetOn In oBook.Worksheets
oSheetOn.Cells(iRow,iCol).value = "This cell at " & iRow & " row, " & iCol & " column in worksheet " & oSheetOn.name
Next
-----------------------------------------------------------
This is just one way to iterate through each spreadsheet in a workbook. It uses a For Each statement to iterate through the worksheet objects in the workbooks.worksheets collection.
Hopefully this answers your VBA question,
Allen.
|
