Thread: 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

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" <colin@NOusSPA4b.co.Muk> wrote in message
news:44ae7c10$0$22125$ed2619ec@ptn-nntp-reader01.plus.net...
> 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
>

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" <chip@cpearson.com> wrote in message
news:Ow%236PsdoGHA.1592@TK2MSFTNGP04.phx.gbl...
> 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" <colin@NOusSPA4b.co.Muk> wrote in message
> news:44ae7c10$0$22125$ed2619ec@ptn-nntp-reader01.plus.net...
>> 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
>>
>
>

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" <chip@cpearson.com> wrote in message
> news:Ow%236PsdoGHA.1592@TK2MSFTNGP04.phx.gbl...
> > 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" <colin@NOusSPA4b.co.Muk> wrote in message
> > news:44ae7c10$0$22125$ed2619ec@ptn-nntp-reader01.plus.net...
> >> 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

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" <petersod@verizonXSPAM.net> wrote in message
news:44AE897C.850BF988@verizonXSPAM.net...
> 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" <chip@cpearson.com> wrote in message
>> news:Ow%236PsdoGHA.1592@TK2MSFTNGP04.phx.gbl...
>> > 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" <colin@NOusSPA4b.co.Muk> wrote in message
>> > news:44ae7c10$0$22125$ed2619ec@ptn-nntp-reader01.plus.net...
>> >> 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

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" <petersod@verizonXSPAM.net> wrote in message
> news:44AE897C.850BF988@verizonXSPAM.net...
> > 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" <chip@cpearson.com> wrote in message
> >> news:Ow%236PsdoGHA.1592@TK2MSFTNGP04.phx.gbl...
> >> > 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" <colin@NOusSPA4b.co.Muk> wrote in message
> >> > news:44ae7c10$0$22125$ed2619ec@ptn-nntp-reader01.plus.net...
> >> >> 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