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