Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 8th, 2009, 05:59 AM
Authorized User
Join Date: Feb 2009
Posts: 16
Thanks: 1
Thanked 0 Times in 0 Posts
Default questions

Hi all

I have statement as part of here’s “ .writescreen Sheets("07").Cells(7, 24), 7, 26 “ that’s constrains by worksheet name ? to create too large the statement to do the same as defilement WS ?

How can I change the code to what’s active sheets to Run instead of specify Worksheet ?

Please help help me
Old April 9th, 2009, 11:13 AM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

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

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,

Similar Threads
Thread Thread Starter Forum Replies Last Post
.NET Interview Questions, C# Interview Questions, dotnetuncle .NET Framework 2.0 4 June 22nd, 2019 07:03 AM
two questions reverand Excel VBA 2 December 5th, 2005 06:33 AM
questions anshul Javascript How-To 1 April 12th, 2004 07:29 AM
many questions Frank1111 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 December 22nd, 2003 09:59 PM
Please go through this questions phpsharma PHP How-To 0 November 4th, 2003 03:26 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.