Excel - vba code to select sheet without a name

Asked By Jimmylaki on 31-Aug-09 06:25 AM
Hello,

I am quite new to vba and trying to write a code which I can select a
sheet and filter a range on the active sheet. The problem is how can I
make the code to select a sheet which has a name on the tab which
chanages daily.

thank you for your suggestions

regards
JL


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=129998




Dave Peterson replied on 31-Aug-09 08:32 AM
My preference would be to select the correct sheet first, then run the macro.  I
could write the code against the activesheet and not have to worry about
selecting a sheet.

If I were really concerned, the code could ask if it should continue:

Option Explicit
Sub Testme()

dim resp as long
dim wks as worksheet

resp = msgbox(Prompt:="This macro will work against the activesheet." _
& vblf & "Continue?", buttons:=vbyesno)

if resp = vbno then
exit sub
end if

set wks = activesheet

with wks
'lots of code here
end with

end sub

=========
For the most part, my generic macros either work on the selected area or on the
activesheet.  I will not usually give the prompt -- especially if it is code that
only I use.


--

Dave Peterson
Don Guillett replied on 31-Aug-09 09:41 AM
This gives you the name of the sheet that is 5th in order from left to right
MsgBox ActiveWorkbook.Sheets(5).Name
so
activeworkbook.sheets(5).select

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Shane Devenshire replied on 31-Aug-09 10:37 AM
Hi,

If you could give us an idea of how the name changes daily, for example is
this because a new sheet is inserted, because the sheet is renamed, or
because you are moving through a set of pre-existing sheets to a different
one each day?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
Jimmylaki replied on 31-Aug-09 11:03 AM
Hello,

the sheets change to reflect various customers which we have to filter
out certian data, when I say the name changes daily it could be the same
customer next Tuesday or Thursday but it is always the same  i was
writing something along the lines of the following but getting lost.

sub sheetwithnoname()

i= 1 to count,

sheets(i).select.range("A1:T").select

etc
end sub


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=129998
Don Guillett replied on 31-Aug-09 11:13 AM
sheets(i).select
.range("A1:T").select
or application goto sheets(i).select.range("A1:T")
or do it withOUT Selecting

for i= 1 to 4
with sheets(i)
.range("a1").copy
end with
next i


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Jimmylaki replied on 01-Sep-09 07:39 AM
Thanks for the help with the code. I have added this into my macro at
work and it is working as I want it to.



Don Guillett;470608 Wrote:


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=129998
Don Guillett replied on 01-Sep-09 08:19 AM
For archival purposes, post your code

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
Jimmylaki replied on 11-Sep-09 05:22 AM
Dim sourceRange As Range
Dim wb1 As Workbook
Dim lastrow As Integer
Dim ws As Worksheet
Dim destSheet As Worksheet

' to open the workbook

Application.ScreenUpdating = False

Set wb1 = Workbooks.Open("J:\projects\test.xls")
Set sourceRange = wb1.Sheets("test1").Columns("B")

For Each ws In Worksheets
Count = wb1.Sheets.Count
For i = 1 To Count
With Sheets(i)
lastrow = Range("A1").End(xlDown).Row
Sheets(i).Range("A1:AU" & lastrow).Select
Sheets(i).Range("A1:AU" & lastrow) = Sheets(i).Range("A1:AI" &
lastrow).Value

End With

On Error Resume Next



Don Guillett;471839 Wrote: