VBA newbie-copy range from one workbook to another

findeep

New Member
Joined
Nov 17, 2011
Messages
7
Hi, struggling out here in Nigeria without much help...trying to copy a range of cells from one worksheet (protected) to a new sheet in another workbook. Wrote it in Excel 2003 and then discovered the client machines are 2002 and I now get an error when I try to copy:


Set NewBook = Workbooks.Add
With NewBook
.SaveAs Filename:=FileNme
End With
'Now copy the required data to blanks sheet in new workbook
BkNme = Workbooks(Workbooks.Count).Name
Workbooks(OpenedLogFile).Worksheets("Ops").Range("B9:O400").Copy Destination:=Workbooks(BkNme).Sheets(1).Range("A1")
ActiveWorkbook.Close SaveChanges:=False

OpenedLogFile is a string populated from the filename of the workbook I'm copying from

Hope someone can point me in the right direction please

Max:eek:
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi Findeep,

Try this...

Range("B9:O400").Select
Selection.Copy
Workbooks.Add
Sheet1.Range("A1").select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\abc\Desktop\Book2.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
 

findeep

New Member
Joined
Nov 17, 2011
Messages
7
Jaafar

the error I'm getting is Run-time error '1004' Copy method of Range class failed

Max
 

findeep

New Member
Joined
Nov 17, 2011
Messages
7

ADVERTISEMENT

Hi mgm05267 and thanks but I'm still getting the run-time error at Selection.Copy....?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,114
Office Version
  1. 2016
Platform
  1. Windows
Are you sure the BkNme String holds the name of the intended workbook ?
 

findeep

New Member
Joined
Nov 17, 2011
Messages
7

ADVERTISEMENT

Are you sure the BkNme String holds the name of the intended workbook ?
Jaafar yes I'm positive-have done a MsgBox immediately before the copy to test it. The thing is I wrote the procedure in Excel 2003 on my own laptop as we are short of work computers here and it works fine-the problem only arises using the work machines which use Excel 2002. The sheet / workbook I am copying from is password protected-that's why I'm copying the data as I need to reformat and delete some of the data prior to putting in a pivot table / chart report-it works without a problem on my Excel 2003 but on 2002 it causes this error, that's why it's so frustrating..trying to modify it all to work!! and it's a long time since I did my VB 5 course (I wasn't that good even then ;)
 

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi Findeep,

before selection of range, make sure that book1 is activated. That is....

Windows("Book1.xls").Activate
Range("B9:O400").Select
Selection.Copy
Workbooks.Add
Sheet1.Range("A1").select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\abc\Desktop\Book2.xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Windows("Book1.xls").Activate
ActiveWindow.Save
ActiveWindow.Close

Please dont forget to reply...
Regards,

MGM
 

findeep

New Member
Joined
Nov 17, 2011
Messages
7
Hi mgm

still doesn't like the copy statement-still giving the run time error
 

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi Findeep,

Try this....


Code:
Sub CopyPaste()

    Range("B9").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="D:\Book2.xlsx",     FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    MsgBox "File Saved Successfully"
    Range("A1").Select
    
End Sub

Regards,

MGM
 

Watch MrExcel Video

Forum statistics

Threads
1,122,494
Messages
5,596,487
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top