This has me completely stumped.....

No error messages, and just the paste statement doesn't work....



wrote:

I spotted the rogue 'Namw' and corrected it before trying your code -
gave the runtime error.

What I completely don't understand is , if I record a macro to do the
steps - which does the copy and paste - why running the recorded code
as part of my own code, the paste doesn't work....!

E


wrote:

With the correct spellings:

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

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

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

Columns("A:E").Copy

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

Raymond

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