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:
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
Are you sure the BkNme String holds the name of the intended workbook ?
 
Upvote 0
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 ;)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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