Selecting Last Sheet

B

Bonbon

Is there any way to select the last Worksheet, other than clicking on the
Last Tab?
Because i created a macro which inserts more sheets after the Last Sheet,
but that Last Sheet is named (lets call it LS), so everytime the macro runs
it inserts new sheets after the LS sheet, rather than the 'real' last sheets.

Please help, i've been trying and trying =(
Thx in advance

Bonbon
 
R

Ron de Bruin

Hi Bonbon

You can use this if you have only worksheets in your workbook

Worksheets.Add after:=Worksheets(Worksheets.Count)

To select
Worksheets(Worksheets.Count).Select
 
B

Bonbon

Sorry im like a beginner to Excel, and i dont understand what you mean, could
you sort of make it clearer what you wanted me to do? thanks

Bonbon
 
P

paul

Ron has given you some code for a macro .I know that ctrl held down while
pressing page up or page dpwn selects the next or previous worksheet,i dont
know if there is a keyboard shortcut to jump to the last sheet the >l symbol
on the bottom left tab will take you to the last tab if it isnt showing on
the screen
 
R

Ron de Bruin

Hi Bonbon
You used code you say that's why I posted code

This line will add a worksheet at the end
Worksheets.Add after:=Worksheets(Worksheets.Count)

And this will select the last worksheet
Worksheets(Worksheets.Count).Select

Have you try this?
 
B

Bonbon

Hi Ron,
Yes your right, i am using macros =)
I understand what you mean now, but i just know where to enter the "And this
will select the last worksheet
Worksheets(Worksheets.Count).Select" but i dont know where to put the 1st
code.
I tried putting it after the 'select last sheet' but it didnt come out
right. Also if i was to insert 5 sheets; what should i change the code to?

"Worksheets.Add after:=Worksheets(Worksheets.Count)"

Please help =( , would it be better if i paste my macro code for u? so you
kno exactly where im goin rong?

Bonbon
 
B

Bonbon

Hi again Ron,
Thank you so much, you code was alot of help to me =D
all i did was just entered it 5 times to add 5 sheets lol.

However theres another problem i came across. Copying the E1 cell and
pasting it as the name of the sheet. Is there a code for this? because when i
record it as a macro, it keeps on copying the actual name of the cell. e.g.
sheet 1, E1 = M-13.06.05, so on the next week (M-20.06.05) it continues to
copy the last M-13.06.05.
I checked on the macro, and it says copy M-13.06.05, i want it to copy the
things inside the cell E1.

Pls help
Thx in advance,
Bonbon
 
R

Ron de Bruin

Hi

Don't know if I understand you correct but this will add a sheet and name it as the cell E1 in "Sheet1"

Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "M-" & Format(Sheets("Sheet1").Range("E1").Value, "dd.mm.yy")
 
B

Bonbon

Thanks so much Ron, it works =)
sorry but i have one more problem lol. Now that the weekdays are out of the
way, i need a macro to rename the weekly sales sheet. This sheet has to be
named like this WSR-13.06.05 - 17.06.05
And in the E1:F1 cell it has 13.06.05 - 17.06.06
i tried this macro, but didnt work:
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "WRS" &
Format(Sheets("Sheet6").Range("E1:F1").Value, "dd.mm.yy - dd.mm.yy")

pls help again =)
Thx in advance,
Bonbon
 
R

Ron de Bruin

If E1 = 13.06.05 and F1 = 17.06.05

Then use this

With Sheets("Sheet6")
.Name = "WRS " & Format(.Range("E1").Value, "dd.mm.yy") _
& " - " & Format(.Range("F1").Value, "dd.mm.yy")
End With
 
B

Bonbon

wow THANK YOU so so much Ron. Thanks for all your help and the fast replies,
i really appreciate this!

Thx again,
Bonbon
 
B

Bonbon

Im so sorry, but another problem has just came up =(
Right now, i have 5 sheets M-13.06.05 to F-17.06.05. And in all those
sheets, theres a field named 'Quantity Sold' ( cell D3). I have the
'WRS-13.06.05 - 17.06.05' sheet which has a field named 'Total Quantity Sold'
(cell D3) and i need all the Monday to Fridays 'QS' to be added up and put
into the WRS sheet's 'TQS'.
I created a Macro which does this, but it only works for the previous week
and not any other future weeks. I know its got to do with the sheet names i
selected (e.g. for the previous week, the monday is M-06.06.05, but this week
it is M-13.06.05.
Is there any way to solve this? or if im asking too much do anyone know how
to make a code for this?

Any help would be hugely appreciated
Thx in advance,
Bonbon
 
R

Ron de Bruin

With code you can do it like this

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Sumsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set Basebook = ThisWorkbook
Set Sumsh = Sheets("WRS-13.06.05 - 17.06.05")

RwNum = 1
'The links to the first sheet will start in row 2

For Each Sh In Basebook.Worksheets
If Sh.Name <> Sumsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1

Sumsh.Cells(RwNum, 1).Value = Sh.Name
'Copy the sheet name in the A column

For Each myCell In Sh.Range("D3") ' <----Change the range
ColNum = ColNum + 1
Sumsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell
End If
Next Sh

Sumsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
 
B

Bonbon

Sorry Ron, im quite new with macros and codes. If i am correct, did you want
me to copy what you posted for a macro code?
If not, i dont understand alot of it (Dim Sh, Dim Sumsh, etc)
sorry to cause you all these hassle =(

Bonbon
 
R

Ron de Bruin

you say
That's why I posted a macro that create the formula's for you
Copy it in a normal module and run it to create the links in
Sheets("WRS-13.06.05 - 17.06.05")

Why don't you create the links manual (only 5)
In Sheets("WRS-13.06.05 - 17.06.05")
Enter = in a cell (to start building the formula)and go the sheet you want and select D3 and press Enter.

Do this for all the sheets
 
B

Bonbon

Hi Ron, i created a macro with ur code, but when you say ''The links to the
first sheet will start in row 2'' and 'Copy the sheet name in the A column',
i dont kno what you mean. Copy the sheet name in the A column? im confused.

Bonbon.
 
R

Ron de Bruin

Hi Bonbon

Try the code in a new workbook and name one sheet
"WRS-13.06.05 - 17.06.05"
You see that in Column A it display the sheet name and in B................. the links when you run the macro
 
B

Bonbon

Oh Ron, im so sorry, i think i confused u.
I have 5 sheet which has values in the same column n rows (D3-D62) and the
WSR has the same space too, but the space in WSR (D3-D63) is the total of all
the sheets ones. e.g. sheet1 D3 = 1 , sheet2 D3 = 1 etc. the WSR sheet's D3
should be 5, it adds up all the sheets cells. And i have made the formula too
(and recorded the steps in a macro) but it keeps on repeating the same week.
Do you know how to solve this?
if i am askin too much im so sorry, then pls just help me on this: whats the
code 'to select the 1st sheet infront of the current sheet', and the '5th
sheet infront of the current sheet' and lastly the 'second to last sheet'.
e.g. the current sheet is WSR-13.06.05 - 17.06.05 and you want to select the
1st sheet infront of this sheet (i need this, because i cant write 'sheetX'
because the sheets are already named, and i cant write 'M-13.06.05' because
it wont work for the future calculations'.

i am really sorry to cause you all this hassle, but i am grateful that you
have helped.

Kindest regards,
Bonbon
 
Top