Using user input to select tab

[wilster2012]

I am modifying a macro in Excel 2010 that copies data from one sheet and pastes it in a format that works with a pivot table. The data in the source worksheet is spread out over several rows. The macro pastes the data from numerous columns to single columns. The name of the source worksheet will change each time it is used, so I am attempting to use Application.InputBox to get the new tab name then copy/paste to the destination worksheet (PVTData). The first copy/paste works fine, then after that the macro copies blank cells from the destination folder and pastes in the same sheet. Code is below:

Sub PVTData()
'
' PVTData Macro
'

'
      Dim strName As String
     Dim ws As Worksheet
'    '   Clear data before copy and paste
    Worksheets("PVTData").Range("A2:F1000").Clear
'
[COLOR=#00ff00]'User enters the name of the tab that will be used for the pivot table[/COLOR]


    strName = Application.InputBox("Please Enter Tab Name")
    strName = strName
     Sheets(strName).Select
     On Error Resume Next
    Set ws = Worksheets(strName)
    If Not ws Is Nothing Then
    'sheet exist

    Else
    [COLOR=#00ff00]'sheet does not exist or the entry is otherwise invalid[/COLOR]

        MsgBox "The sheet doesn't exist or you entered the name incorrectly"

        Exit Sub

    End If
    
    
    [COLOR=#00ff00]'First copy and paste works fine[/COLOR]
    Sheets("strName").Select
    Range("B19:C42").Select
    Selection.Copy
    Sheets("PVTData").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 [COLOR=#00ff00] 'Second time starts copying form the destination sheet (PVTData) to cells also in the destination sheet.[/COLOR]  
  Sheets("strName").Select
   ActiveWindow.LargeScroll Down:=-1
   Range("E6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PVTData").Select
    Range("C2:C25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Any help would be greatly appreciated

Thank
Willy

 

[Fluff]

Remove the quotes

Sheets([COLOR=#ff0000]"[/COLOR]strName[COLOR=#ff0000]"[/COLOR]).Select

 

[Fluff]

You can also rewrite your code like

Sub PVTData()
'
' PVTData Macro
'

'
   Dim strName As String
   Dim ws As Worksheet
'  Clear data before copy and paste
'   Worksheets("PVTData").Range("A2:F1000").Clear
'
'User enters the name of the tab that will be used for the pivot table


   strName = Application.InputBox("Please Enter Tab Name")
   If Not Evaluate("isref('" & strName & "'!A1)") Then
     MsgBox "The sheet doesn't exist or you entered the name incorrectly"
     Exit Sub
   End If
   
   
   'First copy and paste works fine
   Sheets(strName).Range("B19:C42").Copy
   Sheets("PVTData").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

  'Second time starts copying form the destination sheet (PVTData) to cells also in the destination sheet.
   Sheets(strName).Range("E6").Copy
   Sheets("PVTData").Range("C2:C25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

   Application.CutCopyMode = False

getting rid of the .Select & Selection

 

[wilster2012]

That worked great, thanks!

 

[Fluff]

Glad to help & thanks for the feedback