Copying a form to the same sheet using macro
 

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

Copying a form to the same sheet using macro
 

Put this code on a button either in the toolbars or on the Summary Sheet
to copy the latest worksheet. If you put the code on the Week sheet the
code gets created on every new sheet. then if you need to change the
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 the
order of the sheets. So the code looks for the latest sheet in the
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 the
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 Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166042

Microsoft Office Help

Copying a form to the same sheet using macro
 

Hi Joel,

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

Cheers,
AN


joel;599343 Wrote:
Put this code on a button either in the toolbars or on the Summary Sheet
to copy the latest worksheet. If you put the code on the Week sheet the
code gets created on every new sheet. then if you need to change the
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 the
order of the sheets. So the code looks for the latest sheet in the
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 the
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 Sub


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

--
ANaz
------------------------------------------------------------------------
ANaz's Profile: 1351
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166042

Microsoft Office Help

Copying a form to the same sheet using macro
 

See how the macro in the attached workbook runs. I couldn't figure out
in the Summary sheet why you where multiplying in the first row some
entries by .25 and other .3. My results is multiplying everything by
.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 original
requst was a weekly worksheet but the workbook you sent had monthly
sheets. Can you explain.


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

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=166042

Microsoft Office Help