Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Sheet names & Workbook names in VBA coding
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Sheet names & Workbook names in VBA coding
Hi Chip,
Thanks for this. The code (as it stands at the moment) is... Public Sub SelectiveDelete() Dim LRowData As Long, ir As Long With Sheets("Sheet1") ' detect last row of data in column A LRowData = .Cells(Rows.Count, "A").End(xlUp).Row ' scan list testing if the value is in range For ir = LRowData To 1 Step -1 If Trim(.Cells(ir, 1).Value) = "A/C NO" Or _ Trim(.Cells(ir, 1).Value) = "Report Total" Or _ Trim(.Cells(ir, 1).Value) = " Evolut" Or _ Trim(.Cells(ir, 1).Value) = "Evoluti" Or _ Trim(.Cells(ir, 1).Value) = "-------" Or _ InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _ Len(Trim(.Cells(ir, 2).Value)) = 0 _ Then .Rows(ir).Delete Shift:=xlUp Next ir End With End Sub So where would I put your code? Or, due to the coding that I'm using, do I need it worded differently? Thanks, again, for your interest. regards Colin "Chip Pearson" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Sheet names & Workbook names in VBA coding
If you're running the code against the activesheet, you could just change:
With Sheets("Sheet1") to With Activesheet Colin Foster wrote: Hi Chip, Thanks for this. The code (as it stands at the moment) is... Public Sub SelectiveDelete() Dim LRowData As Long, ir As Long With Sheets("Sheet1") ' detect last row of data in column A LRowData = .Cells(Rows.Count, "A").End(xlUp).Row ' scan list testing if the value is in range For ir = LRowData To 1 Step -1 If Trim(.Cells(ir, 1).Value) = "A/C NO" Or _ Trim(.Cells(ir, 1).Value) = "Report Total" Or _ Trim(.Cells(ir, 1).Value) = "Evolut" Or _ Trim(.Cells(ir, 1).Value) = "Evoluti" Or _ Trim(.Cells(ir, 1).Value) = "-------" Or _ InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _ Len(Trim(.Cells(ir, 2).Value)) = 0 _ Then .Rows(ir).Delete Shift:=xlUp Next ir End With End Sub So where would I put your code? Or, due to the coding that I'm using, do I need it worded differently? Thanks, again, for your interest. regards Colin "Chip Pearson" wrote in message ... 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Sheet names & Workbook names in VBA coding
Hi Dave,
I suppose at this point, I should turn yellow, open a can of Duff Beer & utter those famous words... "Doh!!" But then, I suppose,if you use VBA regularly, you'd know this... I don't which is why I'm really grateful for th ehelp on this & other NG's Regards Colin "Dave Peterson" wrote in message ... If you're running the code against the activesheet, you could just change: With Sheets("Sheet1") to With Activesheet Colin Foster wrote: Hi Chip, Thanks for this. The code (as it stands at the moment) is... Public Sub SelectiveDelete() Dim LRowData As Long, ir As Long With Sheets("Sheet1") ' detect last row of data in column A LRowData = .Cells(Rows.Count, "A").End(xlUp).Row ' scan list testing if the value is in range For ir = LRowData To 1 Step -1 If Trim(.Cells(ir, 1).Value) = "A/C NO" Or _ Trim(.Cells(ir, 1).Value) = "Report Total" Or _ Trim(.Cells(ir, 1).Value) = "Evolut" Or _ Trim(.Cells(ir, 1).Value) = "Evoluti" Or _ Trim(.Cells(ir, 1).Value) = "-------" Or _ InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _ Len(Trim(.Cells(ir, 2).Value)) = 0 _ Then .Rows(ir).Delete Shift:=xlUp Next ir End With End Sub So where would I put your code? Or, due to the coding that I'm using, do I need it worded differently? Thanks, again, for your interest. regards Colin "Chip Pearson" wrote in message ... 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Sheet names & Workbook names in VBA coding
Sometimes, just an extra pair (or an extra few pair) of eyes is all it takes to
get to the next problem, er, challenge. Colin Foster wrote: Hi Dave, I suppose at this point, I should turn yellow, open a can of Duff Beer & utter those famous words... "Doh!!" But then, I suppose,if you use VBA regularly, you'd know this... I don't which is why I'm really grateful for th ehelp on this & other NG's Regards Colin "Dave Peterson" wrote in message ... If you're running the code against the activesheet, you could just change: With Sheets("Sheet1") to With Activesheet Colin Foster wrote: Hi Chip, Thanks for this. The code (as it stands at the moment) is... Public Sub SelectiveDelete() Dim LRowData As Long, ir As Long With Sheets("Sheet1") ' detect last row of data in column A LRowData = .Cells(Rows.Count, "A").End(xlUp).Row ' scan list testing if the value is in range For ir = LRowData To 1 Step -1 If Trim(.Cells(ir, 1).Value) = "A/C NO" Or _ Trim(.Cells(ir, 1).Value) = "Report Total" Or _ Trim(.Cells(ir, 1).Value) = "Evolut" Or _ Trim(.Cells(ir, 1).Value) = "Evoluti" Or _ Trim(.Cells(ir, 1).Value) = "-------" Or _ InStr(1, Trim(.Cells(ir, 2).Value), "@") 0 Or _ Len(Trim(.Cells(ir, 2).Value)) = 0 _ Then .Rows(ir).Delete Shift:=xlUp Next ir End With End Sub So where would I put your code? Or, due to the coding that I'm using, do I need it worded differently? Thanks, again, for your interest. regards Colin "Chip Pearson" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View All Sheet Names in Workbook at Once | Excel Discussion (Misc queries) | |||
how do I find names in a workbook full of names | Excel Discussion (Misc queries) | |||
Need sopme help coding Dates and Sheet names. (Please) | Excel Programming | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Retrieving the sheet names of another workbook | Excel Programming |