Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a formatted "template" sheet and naming new sheet via user form | Excel Programming | |||
Copying a cell value to a different sheet with a macro | Excel Programming | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
MACRO for copying active sheet without using a certain name | Excel Programming | |||
copying sheet with macro | Excel Programming |