To select an Input Sheet to Copy

**sreeks** · 08-28-2010, 02:52 PM

Hi All,

The below code was picked up from this forum and one of this forum members has helped in copying the Value and Format and placing it in the new sheet.

I am trying to select a sheet to be copied via macro which is not working. I am absolutely new to coding and request the team to excuse me if there is a silly coding mistake.

Below is the objective:

The macro should give a dialogue box to select the Input Sheet to be copied rather than explicitly specifying "Sheet Name" in the code

Best Regards,
Srikanth

Option Explicit

Dim sName As String
Dim sPic As String

sPic = Application.InputBox("Enter the Sheet To be Copied:", Title:="Source Sheet Title", Type:=2)
    If sPic = "" Then Exit Sub

Dim v: v = Evaluate("ISREF(sPic!A1)")

If v Then
    sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
    If sName = "" Then Exit Sub
    Sheets("sPic").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = sName
Else
    MsgBox "The ""sPic"" cannot be found."
End If

End Sub

Would like to implement the above in the below code:

Option Explicit
Sub NewSheetFromTemplate()
    Dim sName As String
    
    sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
    If sName = "" Then Exit Sub
    
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sName
    
    Sheets("Template").Cells.Copy
    With Sheets(sName).Range("A1")
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
        .PasteSpecial xlPasteValidation
    End With

End Sub

**Marcol** · 08-28-2010, 03:14 PM

Hi Srikanth this effectively a double post.

http://www.excelforum.com/excel-prog...e-a-sheet.html
Although you have marked this thread [Solved], you have left it open ended with the same question you are asking here!

Please go back to your last thread and delete the hanging question.

from

However, what I am also looking in addition to that is as below:......

Cheers

**protonLeah** · 08-28-2010, 04:21 PM

Umm, is this the right thread?? I'm confused (not again! )
--------------------------------------------
sPic is not a string, it is a variable holding a string value:

v = Evaluate("ISREF(" & sPic & "!A1)")
MsgBox "The " & sPic & " cannot be found."
Sheets(sPic).Copy After:=Sheets(Sheets.Count)

**sreeks** · 08-28-2010, 03:32 PM

Hi Marco,

I have updated the previous post and this would be an independent thread.

Best Regards,
Sri

**sreeks** · 08-28-2010, 04:10 PM

Hi Photon,

I am trying to use the logic in the code provided by Marco.
I am unable to execute the code since by default the macro is showing the message "&sPic&" cannot be found.

My question is if we are using sName as string variable and this is being used as the sheet name, then the same way sPic should be a string variable to recognize the existing Sheet Name.

Best Regards,
Sri

Sub  abOutput()
    Dim sName As String
    Dim sPic
    Dim v

    sPic = Application.InputBox("Enter the Sheet To be Copied:", Title:="Sheet Title", Type:=2)
    If sPic = "" Then Exit Sub

    v = Evaluate("ISREF(" & sPic & "!A1)")
    MsgBox "The " & sPic & " cannot be found."
    Sheets(sPic).Cells.Copy
    
    sName = Application.InputBox("Enter the New Sheet Name:", Title:="New Sheet Title", Type:=2)
    If sName = "" Then Exit Sub
    
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sName
    
    With Sheets(sName).Range("A1")
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
        .PasteSpecial xlPasteValidation
    End With

End Sub

**sreeks** · 08-28-2010, 04:32 PM

Hi Photon,

Sorry for the confusion.
I had to close the open ended question in the previous thread since that thread was marked as "Solved".

I changed the code as per your advise and ran it. By default it throws the error message "Sheet cannot be found.

After this message, the message "Enter the New Sheet Name" is popping up.

Below is the code for your reference:

Sub abOutput()
    Dim sName As String
    Dim sPic
    Dim v

    sPic = Application.InputBox("Enter the Sheet To be Copied:", Title:="Sheet Title", Type:=2)
    If sPic = "" Then Exit Sub

    v = Evaluate("ISREF(" & sPic & "!A1)")
    MsgBox "The " & sPic & " cannot be found."
    Sheets(sPic).Copy After:=Sheets(Sheets.Count)
    
    sName = Application.InputBox("Enter the New Sheet Name:", Title:="New Sheet Title", Type:=2)
    If sName = "" Then Exit Sub
    
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = sName
    
    With Sheets(sName).Range("A1")
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
        .PasteSpecial xlPasteValidation
    End With

End Sub

What I also did was I deleted the below section from the code:

v = Evaluate("ISREF(" & sPic & "!A1)")
    MsgBox "The " & sPic & " cannot be found."

Now the error is

"Run Time Error '1004'.
PasteSpecial Method of Range class failed

Regards,
Sri

**protonLeah** · 08-28-2010, 04:51 PM

Sorry again! The lines I provided were only meant to replace the original lines, leaving all others as is:
(tested: works)

Option Explicit
Sub test()
    Dim sName As String
    Dim sPic As String
    Dim v
    
    sPic = Application.InputBox("Enter the Sheet To be Copied:", Title:="Source Sheet Title", Type:=2)
        If sPic = "" Then Exit Sub
    
    v = Evaluate("ISREF(" & sPic & "!A1)")
    If v Then
        sName = Application.InputBox("Enter the new sheet name:", Title:="New Sheet Title", Type:=2)
        If sName = "" Then Exit Sub
        Sheets(sPic).Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = sName
    Else
        MsgBox "The " & sPic & " cannot be found."
    End If
    
End Sub

**sreeks** · 08-28-2010, 04:57 PM

Hi Proton,

The code works fine, just that I would like to copy only the Format and Values and not formula from the Input Sheet.

Regards,
Sri

**sreeks** · 08-28-2010, 05:22 PM

Hi Proton,

I am half through the problem which I am doing manually at the moment.

I was referring to the below objectives:
1. Would like to copy only the Format and Values in the new sheet
2. Would like to copy only certain portion of the Input Sheets and not the entire sheet.

Attached is for your reference.

Regards,
Sri