I just realized that "NewSheet" was spelled as "newSheet" in the last line.
The Case difference will definitely cause an error message.
--
Best wishes,

Jim


" wrote:

Hi Raymond

Thanks, but errors on the last line...........

Run time error 9: subscript out of range

And therefore doesn't do the paste.

E

wrote:

Try this:

' The workbook you are copying from
ThisBook=ActiveWorkbook.Name
ThisSheet=ActiveSheet.Name

Workbooks.Add
NewBook=ActiveWorkbook.Name
NewSheet=Activesheet.Namw

Windows(ThisBook).Activate
Sheets(ThisSheet).Select

Columns("A:E").Copy

Workbooks(NewBook).Sheets(newSheet).Range("A1").Pa steSpecial

Raymond

wrote:
Apologies in advance for the basic & clunky code!

I have a workbook containing columns of data and I want to copy and
paste some of the columns into a new workbook, save and close it and
continue with some other manipulations (all of which work fine).

The problem I am having is that when I run the code, it does not error,
but whilst it does the save and close, it doesn't paste anything into
the new workbook.
(even if I record the steps and paste the recorded macro into my code,
it still doesn't do the paste)

Columns("A:E").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:=Worksheets("Notes").Range("auditpath").V alue &
Workbooks("Original.xls").Worksheets("Notes").Rang e("audit") & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWindow.Close

I feel I must be missing something obvious....?

Thanks in advance