Excel VBA to Copy Sheet

Microsoft Excel 2003 (full)
November 16, 2010 at 16:37:09
Specs: Windows XP
Can't get this to work. Help!
Dim sReportFileNameA1 As String
sReportFileNameA1 = InputBox("Enter the Report Name.")
Sheets(" & sReportFileNameA1 & ").Select
ActiveWindow.SelectedSheets.Copy
This code doesn't copy the worksheet I specify. What's wrong?


See More: Excel VBA to Copy Sheet

Report •

#1
November 16, 2010 at 17:36:15
Drop the quotes and the ampersands.

When a value is assigned to a variable, you just use the variable:

   Sheets(sReportFileNameA1)

Also:

You rarely have to Select an object in VBA to perform an operation on it. You can refer to the object and perform the operation in one step:

   Sheets(sReportFileNameA1).Select
    ActiveWindow.SelectedSheets.Copy

can be reduced to:

   Sheets(sReportFileNameA1).Copy

Heck, if you wanted to, the whole thing could be reduced to one line:

Sub CopySheet()
  Sheets(InputBox("Enter the Report Name.")).Copy
End Sub

Ain't VBA fun?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 18, 2010 at 14:16:42
How do I copy just the values and formats to the "new" workbook? I do not want the formulas.

VBA is fun! (But very challenging.)


Report •

#3
November 18, 2010 at 19:08:25
You can try this...

After the code copies the sheet to the new workbook, it will Copy and PasteSpecial...Values all of the cells.

Sub CopySheet()
'Get sheet name and copy it to a new workbook
  Sheets(InputBox("Enter the Report Name.")).Copy
'Copy/paste values in new workbook
   With ActiveWorkbook
    .Sheets(1).UsedRange.Copy
    .Sheets(1).UsedRange.PasteSpecial (xlPasteValues)
       Application.CutCopyMode = False
      .Sheets(1).Range("A1").Select
   End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
November 19, 2010 at 03:49:30
Brilliant! Thanks.

Report •

Ask Question