Selecting Last Sheet
 

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

Selecting Last Sheet
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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
--
paul
remove nospam for email addy!



"Bonbon" wrote:

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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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



"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

Selecting Last Sheet
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

Selecting Last Sheet
 

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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

Selecting Last Sheet
 

you say
I created a Macro which does

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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

Selecting Last Sheet
 

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.

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

Selecting Last Sheet
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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.

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

Selecting Last Sheet
 

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

"Ron de Bruin" wrote:

you say
I created a Macro which does

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





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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


"Bonbon" wrote:

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

Thx again,
Bonbon

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

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")


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message ...
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

"Ron de Bruin" wrote:

Hi Bonbon

Because i created a macro which inserts more sheets after the Last
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?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bonbon" wrote in message
...
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