Paste into new Excel book not working

[elkebubble]

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").Value &
Workbooks("Original.xls").Worksheets("Notes").Range("audit") & ".xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWindow.Close

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

Thanks in advance

 

[raymond.allan]

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").PasteSpecial

Raymond

 

[elkebubble]

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

 

[Jim Jackson]

I would just break the last line down this way.

Workbooks(NewBook).Activate
Sheets(newSheet).activate
Range("A1").PasteSpecial

 

[Jim Jackson]

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

 

[raymond.allan]

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").PasteSpecial

Raymond

 

[elkebubble]

Thanks Jim,

I don't get the error message - but I still don't get the data pasted
into the new workbook!

E

PS. If it makes any difference, it's Excel 2003 SP2 .......

 

[elkebubble]

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

 

[raymond.allan]

Rather than copy columns just copy the range

Count = Range("A65000").End(xlUp).Row

Change the "A" to whatever column holds the most data

Range("A1:E" & Count).Copy rather than Columns("A:E").Copy

Raymond

 

[elkebubble]

This has me completely stumped.....

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