Vba copy sheet and rename with input box then hide new sheet

[Dansecon09]

Hello fairly new with vba but having some issues... I have a workbook with 3 sheets. Lets say sheet A sheet B and sheet C. Sheet B is a a form with data entered in certain say cell C5 , F11 and G15. In a nut shell I want to make an archive by copying a sheet, rename it, hide it, and clear the original forms data.

Specifically I want to
-Copy sheet B
-Rename the copied sheet via an input box "Please rename the sheet" (if the entered name exists already the input box to pop again stating name already exists "please enter a new name")
-Hide the newly renamed sheet
- return to sheet B, clear the data from cells C5, F11, and G15
-have a msgbox stating the "The sheet has been Archived"

The first time I run it, it functions fine. Just as intended. I named the new copied sheet "blue" When I run it a second time and enter the the name "red" It does not rename the newest copied sheet B but renames sheet Blue as Red. The newest copied sheet B is named sheet B (2) instead of Red.

 

[mumps]

Try:

Sub CreateSheet()
    Application.ScreenUpdating = False
    Dim wsName As String
    wsName = InputBox("Please enter the sheet name.")
    If wsName = "" Then Exit Sub
    If Not Evaluate("isref('" & wsName & "'!A1)") Then
        Sheets("B").Copy after:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = wsName
            .Visible = False
        End With
        Sheets("B").Range("C5, F11,G15").ClearContents
    Else
        MsgBox ("The sheet " & wsName & " already exists." & Chr(10) & "Please enter a new name.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[FTMess]

I have a similar issues has above but I don't need to copy the whole sheet. I need to copy just a Column range say Column F3:F65 in above code from Sheet B. Any help would be great. I have modified the above code to meet my needs but I'm getting the Compile Error: Named argument not found.

 

[mumps]

Please post your code using code tags not as a picture. Explain in detail what you want to do referring to specific cells, ranges and sheets.

 

[FTMess]

Ok sorry for that not sure on the code tags. But to be more specific I have 5 worksheets in this workbook. Multiple user will be filling in data into work sheet. Let’s say the worksheet is called A&U. In columns I2 through T2 on sheet “A&U” are the Months. Starting with Jan in I2 ending with Dec in T2. The users will be inputting data into I3:T65. Each column is a month worth of data so they will be working with one column at a time each month starting with column I. In column F the user has the ability to write free text/comments for each Row in column F up to F65. So example data in F3 “comments” column would reflect the data in I3 “Jan”. Input column. F4 would go with I4 etc. All the way down to F65 and I65. At the end of the month I would like to archive all the data in column F only to a new sheet say column D3:D65. Then on the next month Feb all the data in column F on the same sheet A&U will be updated with new comments. At the end of Feb I would like to Archive all the new comments in Column F to the same sheet that Jan comments were archived to but in the next empty column E3:E65. And so on for each month until Dec. The main objective is to archive the comments for each month without loosing them for the year. Sorry for the long read. Any help would be greatly appreciated. Ask any questions and I’ll try to be more clear if you don’t under stand. I have been trying to do this for a long time with out any luck. Thanks in advance.

 

[bebo021999]

Every new month, column F should be deleted manually.
Insert new sheet, named "Comment" to store comment history form column D
Below code is placed in sheet A&U even code (right click on sheetname/View code)
Any change in column F will be triggerred, then column F will be copied and pasted into corresponding column in sheet "Comment"
For testing purpose, I use cell F1 to store date. In actual file, can input today's date, or using month(date) directly in code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim curM&
Application.ScreenUpdating = False
curM = Month(Range("F1").Value) ' for testing, manual input date in F1. With actual file, input =today() in F1, or use curM=month(date) inside code
If Intersect(Target, Range("F3:F65")) Is Nothing Then Exit Sub
    Range("F3:F65").Copy
    Worksheets("Comment").Cells(3, curM + 3).PasteSpecial Paste:=xlPasteValues 'if Jan from column D: +3
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[FTMess]

Thanks

bebo021999 This is exactly what I needed I have been trying to do this for a long time. I give 5 stars.​