beginner with macro question...can't copy data from one workbook into another with macros

[kcphila]

I’m trying to build a macro that copies a selection of columns out of a workbook, opens a new workbook, pastes the selection into that workbook and then saves it. I built the macro, and it ran fine. Then I tried to recreate it in a different workbook, and it doesn’t. It gives the error “Subject out of range” and highlights “ Windows("Book").Activate “ as the problem in VBA. I’m assuming what it’s saying is that it can’t create a new workbook. I can’t understand how this could work once and then not work again, my only theory is that it has to do something with the naming of the workbook I’m creating. I’ve also tried to create a work around by creating a separate workbook then creating a macro to open that workbook and paste my content into it. The problem here is that it gives me an error about how I can’t use the paste function multiple times in a macro.

Help!

Thanks.


Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("B:B,C:C").Select
Range("C1").Activate
Selection.Copy
Workbooks.Add
Windows("2014Workbook.xlsx").Activate
Windows("Book1").Activate
ActiveSheet.Paste
Application.Left = 204.25
Application.Top = 78.25
Application.CutCopyMode = False
ChDir "C:\Users\Kevin Cox\New Query Small Orders\macros"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Kevin Cox\New Query Small Orders\macros\Book1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Application.Left = -228.5
Application.Top = 76.75
ActiveWindow.Close
End Sub

 

[Excalibur13]

Hi Kevin

Use below. Reason you are getting an error is because the workbook you are adding might not be called Book1 and therefore when it's trying to activate that window it cannot find it. Below is not the best way to do it but it gets the job done.

Sub Macro1()
Set wrk = ActiveWorkbook
Set newwrk = Workbooks.Add
wrk.Activate
Range("B:B,C:C").Copy
newwrk.Activate
ActiveSheet.Paste
newwrk.SaveAs Filename:= "C:\Users\Kevin Cox\New Query Small Orders\macros\Book1.xlsx"
newwrk.close
End Sub

 

[kcphila]

Thank you!