Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

I would just break the last line down this way.

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

--
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

Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

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 .......


Jim Jackson wrote:

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

Paste into new Excel book not working
 

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

Paste into new Excel book not working
 

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

wrote:
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 .......


Jim Jackson wrote:

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

Paste into new Excel book not working
 

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