2014-02-05, 08:52 #1
- Join Date
- Feb 2014
- Thanked 0 Times in 0 Posts
VBA code for copying Sheet from One workbook to Another workbook
I am new to VBA. I want to copy a sheet from one workbook to another workbook. I have following VBA code. I am getting "Run-time error '9': Subscript out of range. Please help.
Dim SDrv As String
Dim DDrv As String
Dim Sfname As String
Dim Dfname As String
SDrv = "Y:\Main\" ' Source Drive
Sfname = "Mailing Lists.xlsx"
DDrv = "C:\Test\" ' Destination Drive
Dfname = "Test 05FEB2014.xlsx"
Workbooks.Open (SDrv & Sfname)
Windows(SDrv & Sfname).Activate
Workbooks(DDrv & Dfname).Sheets("List").Delete ' Delete the sheet if it already exists (Override the sheet)
Sheets("List").Copy Before:=Workbooks(DDrv & Dfname).Sheets("List")
Workbooks(SDrv & Sfname).Close
Workbooks(DDrv & Dfname).Save
Workbooks(DDrv & Dfname).Close
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-02-05, 08:56 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 755 Times in 691 Posts