Creating a new sheet and copying it's data to an existing sheet using MACROS

[harry71152]

Hi there,


I'm fairly new to Macros and I'm having a hard time trying to figure this situation out.


I'm trying to create a macro which would create a new sheet, ask for a name and be ready to enter data into it. The data entered into that new sheet should be updated on an existing sheet.


Here is what I've figured out so far:


For example, I have a workbook named Timesheet with 3 sheets named Final, Time Sheet and Time Sheet 1. I've already created a macro named "New Sheet". Whenever I click on "New Sheet", it creates a new sheet and asks for a name everytime. Here is the code for that macro:

Sub AddSheetz()

'First, jump through the validation hoops

Dim AddSheetQuestion As Variant

'Define the application input box question

showAddSheetQuestion:

AddSheetQuestion = Application.InputBox("Enter the week ending date," & vbCrLf & _

"or click the Cancel button to cancel:", _

"What sheet do you want to add?")

'Cancel or the X was clicked

If AddSheetQuestion = False Then

MsgBox "You clicked the Cancel button." & vbCrLf & "No new sheet will be added.", 64, "Cancel was clicked."

Exit Sub

'OK was clicked without anything being entered

ElseIf AddSheetQuestion = "" Then

MsgBox "You clicked OK but entered nothing." & vbCrLf & vbCrLf & _

"Please type in a valid sheet name." & vbCrLf & "Otherwise, you must click Cancel to exit." & vbCrLf & vbCrLf & _

"Click OK and let's try again.", 48, "Hmmm...that didn't make sense..."

GoTo showAddSheetQuestion

End If

'See if a worksheet exists that is named as the new name being attempted to add.

'We want this code to error, because if it does, it will mean no such sheet exists

'so we can complete this macro.

On Error Resume Next

Worksheets(UCase(AddSheetQuestion)).Activate

If Err.Number <> 9 Then

Err.Clear

GoTo ErrorHandler1

End If

'Error trap #2 for naming syntax error

On Error GoTo ErrorHandler2

'Here's the actual sheet addition code

With Application

.ScreenUpdating = False

.EnableEvents = False

.DisplayAlerts = False

End With

'Add and name the new sheet

Worksheets.Add

With ActiveSheet

.Name = AddSheetQuestion

.Move After:=Worksheets("Time Sheet 1")

End With

'Make the Template sheet visible, and copy it

With Worksheets("Time Sheet")

.Visible = xlSheetVisible

.Activate

End With

Cells.Copy

'Re-activate the new worksheet, and paste

Worksheets(AddSheetQuestion).Activate

Cells.Select

ActiveSheet.Paste

With Application

.CutCopyMode = False

.Goto Range("A1"), True

End With

Worksheets("Time Sheet").Visible = xlSheetVeryHidden

With Application

.ScreenUpdating = True

.EnableEvents = True

.DisplayAlerts = True

End With

ActiveWindow.Zoom = 90

ActiveWindow.Zoom = 80

'Inform the user the macro is completed

MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", 64, "Sheet addition successful."

Exit Sub

'If a worksheet exists named with the same three-character code

ErrorHandler1:

MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." & vbCrLf & vbCrLf & _

"Please click OK, verify the name you really" & vbCrLf & _

"want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", 48, "Sorry, that name already taken."

Exit Sub

'If a sheet naming syntax occurs:

ErrorHandler2:

Application.DisplayAlerts = False

ActiveSheet.Delete

Application.DisplayAlerts = True

MsgBox "You entered a character that cannot be part of a sheet name." & vbCrLf & _

"Sheet names cannot contain '':'', ''/'', '''', ''?'', or ''*''.", 16, "Name syntax error."

Exit Sub

End Sub

Here is what I can't figure out how to do:


The data that I need to enter in the new sheet should also be copied to "Time Sheet 1", so "Final" can summarize the data. This should happen everytime I add a new sheet. I already have all the formulas in "Final", but I just can't seem to figure out how to update the times in that sheet everytime a new one is added. If there's an alternative or easier way to do this, I'd certinaly be interested in finding that out.


Any help in this case is greatly appreciated!!


Thanks

 

[SirJB7]

Hi, Harry71152!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Creating%20a%20new%20sheet%20and%20copying%20it%27s%20data%20to%20an%20existing%20sheet%20using%20MACROS%20%28for%20harry71152%20at%20chandoo.org%29.xlsm


I couldn't replicate all your scenario since you didn't provide a sample workbook nor the worksheets structure and formulas. What I could only do was to set some data in worksheet "Time Sheet" and "Time Sheet 1", include your exact code, run it, and check that it replicates correctly in the new created worksheet (from "Time Sheet").


For further assistance please consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!