vba code to select sheet without a name

[Jimmylaki]

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

 

[Dave Peterson]

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 won't usually give the prompt -- especially if it's code that
only I use.

 

[Don Guillett]

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

 

[Shane Devenshire]

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?

 

[Jimmylaki]

Hello,

the sheets change to reflect various customers which we have to filte
out certian data, when I say the name changes daily it could be the sam
customer next Tuesday or Thursday but it is always the same i wa
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 su

 

[Don Guillett]

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

 

[Jimmylaki]

Thanks for the help with the code. I have added this into my macro a
work and it is working as I want it to.

 

[Don Guillett]

For archival purposes, post your code

 

[Jimmylaki]

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