Hi,

I am new to VBA and have encountered a problem, any help would be most welcome.

I have a macro which works fine when run from the bar at the top ie Tools> Macro>Macros etc.

All the macro does is create a new workbook and then copy the details from the original sheet to the new sheet in the workbook..

What we require is to put a button the spreadsheet so we can run the macro from a button (command) .

Normally I would code the button with the macro code however when I try this I get the following error:

Runtime error 1004 Select Method of range class failed, it fails on the cells.select line below



Private Sub CommandButton1_Click()


Dim NewBook As Workbook
Set NewBook = Workbooks.Add

Dim ThisBook As Workbook
Set ThisBook = ThisWorkbook

Sheets(1).Name = "a"
Sheets(2).Name = "b"
Sheets(3).Name = "c"

ThisBook.Activate
Sheets(1).Activate

Application.ScreenUpdating = False

Dim iCount As Integer

For Each Worksheet In Application.Worksheets

Application.Visible = True

W = Worksheet.Name
Worksheet.Activate
Application.Visible = True
Cells.Select
Selection.Copy
Application.CutCopyMode = False
Set NewBook = Workbooks.Add
NewBook.Activate
iCount = Worksheets.Count
NewBook.Sheets.Add after:=Worksheets(iCount)
Sheets(iCount + 1).Name = W
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
ThisBook.Activate
Application.CutCopyMode = False
Sheets(W).Activate
Range("A1").Select
Next

ThisBook.Activate
Sheets(1).Activate
NewBook.Activate

End Sub


Thankyou