I use a large spreadsheet on a daily basis with 31 worksheets (one for each day of the month). Each worksheet is huge.I would like to insert another sheet in the workbook and insert a button to copy another worksheet to the end of the workbook and then rename the sheet to the correspondingday of the month?
Submitted by: AdministratorIf you worksheet names are numeric, this will do the trick:
Public Sub CopyIt()
' This macro will only work if your sheet names are numeric
' Example: the sheet for the first day is 1
'
' Count how many sheets are in this workbook
LastSheet = ActiveWorkbook.Sheets.Count
' What is the name of the last sheet?
LastName = ActiveWorkbook.Sheets(LastSheet).Name
' Add one to the last name to get the new name
NewName = LastName + 1
' Make a Copy of the last sheet
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(LastName)
'Rename the sheet for today
NewLast = LastSheet + 1
ActiveWorkbook.Sheets(NewLast).Name = NewName
End Sub
Submitted by: Administrator
Public Sub CopyIt()
' This macro will only work if your sheet names are numeric
' Example: the sheet for the first day is 1
'
' Count how many sheets are in this workbook
LastSheet = ActiveWorkbook.Sheets.Count
' What is the name of the last sheet?
LastName = ActiveWorkbook.Sheets(LastSheet).Name
' Add one to the last name to get the new name
NewName = LastName + 1
' Make a Copy of the last sheet
ActiveWorkbook.Sheets(LastSheet).Copy after:=Worksheets(LastName)
'Rename the sheet for today
NewLast = LastSheet + 1
ActiveWorkbook.Sheets(NewLast).Name = NewName
End Sub
Submitted by: Administrator
Read Online Microsoft Excel Job Interview Questions And Answers
Top Microsoft Excel Questions
☺ | What are Excel Spreadsheet Number Formats? |
☺ | Using Excel is there a way to close all open Excel files at once instead of closing them one at a time? |
☺ | How do I combine different chart types into my Excel spreadsheet? |
☺ | In MS Access, the long and short date option does not show 4 digit years. How do I achieve the mm/dd/yyyy format? |
☺ | How to Change the Sheet Tab Color of Excel Spreadsheets? |
Top Application Program Categories
☺ | AutoCAD Interview Questions. |
☺ | Microsoft Office Interview Questions. |
☺ | Microsoft Outlook Interview Questions. |
☺ | Microsoft Excel Interview Questions. |
☺ | WPF Interview Questions. |