PDA

View Full Version : VBA to copy a new sheet based on active sheet



kwesmc
02-05-2018, 10:06 AM
I am trying to copy a new sheet based on the active sheet and rename it using an input box. I then want to copy a sheet named "January" and copy and paste that data into the worksheet I just created, but it needs to be a variable named worksheet as I add a new sheet each month.
In the code below, "March" in this case will be named to April for the following month.
I hope this explanation makes sense. I can provide the workbook if needed.

Can someone please help. Here is what I have so far.
Thanks in advance,
Ken Mc


Sub InsertDuplicateSheet()
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = InputBox("Please insert the name of the new sheet.", "Rename Sheet")
Sheets("January").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("March").Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select

End Sub

yujin
02-05-2018, 09:36 PM
You copy all of the cells in the sheet "January" to the new sheet, so why don't you copy the sheet "January" right from the beginning?

kwesmc
02-06-2018, 07:06 AM
Because I add a sheet each month and I don't want to have to go in and change the code to "April", "May", and so on, thus, I wanted to know if there was a way to avoid that.

Paul_Hossler
02-06-2018, 07:10 AM
How about checking to see if "February" exists and then copy "January" as "March", etc.?

kwesmc
02-06-2018, 08:29 AM
Thanks Paul but I'm not understanding you. Yes, "Feb" exists, but I would still have to modify my code, right?

Paul_Hossler
02-06-2018, 12:46 PM
Like this




Option Explicit

Sub etc()
Dim sNextMonth As String, sMonth As String
Dim i As Long, n As Long


For i = 1 To 12
sMonth = Format(DateSerial(2018, i, 1), "mmmm")
n = -1
On Error Resume Next
n = Worksheets(sMonth).Index
On Error GoTo 0

'if worksheet sMonth does not exist then sMonth the next month
If n = -1 Then
sNextMonth = sMonth
Exit For
End If
Next i

MsgBox sNextMonth
End Sub

kwesmc
02-06-2018, 03:19 PM
Thanks Paul, but I'm not sure how to use this to select the next month to paste using the code you sent.
Somehow I need to replace this part of the code using a variable to select the sheet I just created, ie....."March"
Sheets("March").Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select

Paul_Hossler
02-06-2018, 03:32 PM
Because I add a sheet each month and I don't want to have to go in and change the code to "April", "May", and so on, thus, I wanted to know if there was a way to avoid that.


If (very big IF) I'm understanding what you want to do, this copies the ActiveSheet and renames it the next available month

If Jan exist in the workbook, the copy is named Feb. If Feb exists the copy is named March, etc.



Option Explicit

Sub CopyCurrentSheetAndBumpMonth()
Dim sNextMonth As String, sMonth As String
Dim i As Long, n As Long


For i = 1 To 12
sMonth = Format(DateSerial(2018, i, 1), "mmmm")
n = -1
On Error Resume Next
n = Worksheets(sMonth).Index
On Error GoTo 0

'if worksheet sMonth does not exist then sMonth the next month
If n = -1 Then
sNextMonth = sMonth
Exit For
End If
Next I

ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = sNextMonth
End Sub

yujin
02-07-2018, 12:37 AM
ActiveSheet.Name = InputBox("Please insert the name of the new sheet.", "Rename Sheet")

In your code, do you enter "March" with this input box?

Paul_Hossler
02-07-2018, 06:40 AM
No -- there is no need for an InputBox

If the sheet "February" exists then the copy is automatically named "March"

Paul_Hossler
02-07-2018, 07:06 AM
Do you really need to manually enter the next month?

yujin
02-07-2018, 06:40 PM
Do you really need to manually enter the next month?

I have the same question.