View Single Post
  #2   Report Post  
Old July 7th 06, 04:35 PM posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7,247
Default Using Sheet names & Workbook names in VBA coding

Instead of writing code like

Worksheets("Sheet1").Rows(1).Delete

you can have to macro refer to whatever sheet is active in Excel.

ActiveSheet.Rows(1).Delete

If you want this to work on all worksheets at one time, use

Dim WS As Worksheet
For Each WS In Worksheets
WS.Rows(1).Delete
Next WS




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Colin Foster" wrote in message
...
Hi,
I've got a macro working (thanks to help from this NG) to
delete certain specified rows of data from a spreadsheet.
Unfortunately, I have to go into the VBA code to change th
esheet name from "Sheet1" to the actual name of the sheet. As I
want to be able to use this code on more than one sheet (&
possibly across workbooks) is there a simple line of code that
I can put into the VBA to automatically put in th ecorrect
sheet name & file name?

Regards
Colin Foster