Copying a form to the same sheet using macro

[AN]

Hi,
Hope I can clearly demonstrate the problem…..I am not so good in
these….
In “Jan ‘10” sheet I have created a form to capture the sales for week
1. If I want to copy Week 1 sale and make another form in the same
sheet using macro…..how do I do that. When I copy week 1 form to week
2…..next I want to copy week 2 to week 3 by clicking a command button
and I would like to do this using the same code.

Next problem is, I want the summary to add up in a “Summary” sheet. Is
it possible to add up totals as I include the new forms (i.e. week 2,
week 3)?

If someone could help….that would be greatly appreciated.

Thanks in advance,
AN

 

[joel]

Put this code on a button either in the toolbars or on the Summary Shee
to copy the latest worksheet. If you put the code on the Week sheet th
code gets created on every new sheet. then if you need to change th
code there will be up to 52 copies of the code.

I made this code very robust to handle a lot of different situations.
I don't know if the sheets are in order since people can switch th
order of the sheets. So the code looks for the latest sheet in th
format week X. Then creates a new sheet called week (X + 1).

I don't know enough about the data in each sheet or the what th
summary sheet looks like to write code to make the summary sheet.


Private Sub CommandButton1_Click()

'Add new sheet
WkNum = 0
For Each Sht In Sheets
'only look at sheets starting with Week
If Left(UCase(Sht.Name), 4) = "WEEK" Then
SheetName = Sht.Name
'make sure there is a space in the sheet name
If InStr(SheetName, " ") Then
'get the string after the last space
ShtNum = _
Trim(Mid(SheetName, InStrRev(SheetName, " ")))
'make sure it is a number
If IsNumeric(ShtNum) Then
ShtNum = Val(ShtNum)
'only save the highest week num
If ShtNum > WkNum Then
WkNum = ShtNum
End If
End If
End If
End If
Next Sht

'test is a sheet with Week X was found
'and if so create new sheet
If WkNum <> 0 Then
Set NewestSht = Sheets("Week " & WkNum)
'copy latest week worksheet and add copy to end of sheets
NewestSht.Copy after:=Sheets(Sheets.Count)
'name new sht wit correct week number
ActiveSheet.Name = "week " & (WkNum + 1)
End If


End Su

 

[ANaz]

Hi Joel,

Thank you very much for your earlier response. Would you please have
look at the attached workbook and suggest me some better idea to get th
costs calculated into the 'Summary' sheet? I want to add extra form i
needed into each month and get the total calculated without manual inpu
in summary sheet.....is that possible?

Cheers,
AN

Put this code on a button either in the toolbars or on the Summary Shee
to copy the latest worksheet. If you put the code on the Week sheet th
code gets created on every new sheet. then if you need to change th
code there will be up to 52 copies of the code.

I made this code very robust to handle a lot of different situations.
I don't know if the sheets are in order since people can switch th
order of the sheets. So the code looks for the latest sheet in th
format week X. Then creates a new sheet called week (X + 1).

I don't know enough about the data in each sheet or the what th
summary sheet looks like to write code to make the summary sheet.


Private Sub CommandButton1_Click()

'Add new sheet
WkNum = 0
For Each Sht In Sheets
'only look at sheets starting with Week
If Left(UCase(Sht.Name), 4) = "WEEK" Then
SheetName = Sht.Name
'make sure there is a space in the sheet name
If InStr(SheetName, " ") Then
'get the string after the last space
ShtNum = _
Trim(Mid(SheetName, InStrRev(SheetName, " ")))
'make sure it is a number
If IsNumeric(ShtNum) Then
ShtNum = Val(ShtNum)
'only save the highest week num
If ShtNum > WkNum Then
WkNum = ShtNum
End If
End If
End If
End If
Next Sht

'test is a sheet with Week X was found
'and if so create new sheet
If WkNum <> 0 Then
Set NewestSht = Sheets("Week " & WkNum)
'copy latest week worksheet and add copy to end of sheets
NewestSht.Copy after:=Sheets(Sheets.Count)
'name new sht wit correct week number
ActiveSheet.Name = "week " & (WkNum + 1)
End If


End Su

+-------------------------------------------------------------------
|Filename: Forecasting New.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=409
+-------------------------------------------------------------------

 

[joel]

See how the macro in the attached workbook runs. I couldn't figure ou
in the Summary sheet why you where multiplying in the first row som
entries by .25 and other .3. My results is multiplying everything b
.25. I'm not using a formula, instead putting values into the results.

Did you get the macro running to add the new sheets? You origina
requst was a weekly worksheet but the workbook you sent had monthl
sheets. Can you explain

+-------------------------------------------------------------------
|Filename: Forecasting New.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=410
+-------------------------------------------------------------------