export to CSV file suddenly stops working

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
The following code was working fine when I used it to save "a1:F23" from "Payment" sheet into a CSV file.

Sheets("Payment").Select
Workbooks.Add Template:=xlWBATWorksheet
Sh.Range("a1:F23").Copy Destination:= _
ActiveWorkbook.Worksheets(1).Range("A1")
ActiveWorkbook.SaveAs Filename:="T:\act\PAYMENT_at_" & _
Format(Now, "yyyy_mm_dd_hh_mm_ss") & ".csv", _
FileFormat:=xlCSV
ActiveWorkbook.Close SaveChanges:=False

Now the same code gives a Run-time error '9':
Subscript out of range

and it highlights
Sh.Range("a1:F23").Copy Destination:= _
ActiveWorkbook.Worksheets(1).Range("A1")


Any idea why this is happening and how to fix it?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi exceltadpole,

I notice that Sh is not defined in the code you posted. Sh must either be the CodeName of a worksheet (unlikely), or must be a Range type variable that has had a worksheet assigned to it using a Set statement, like

Set Sh = Worksheets("Payees")

Another possibility is that Sh IS the first worksheet in the active workbook, so it is trying to paste to itself.

Damon
 

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
Hi Damon,

Thank you. I look at the code.
Sh was defined a few lines above.
But it did not contain the "Payment" sheet name any more.
This assignment had changed which is why the error message was being reported.
By re-assigning this just before the code, I was able to solve this problem.
Your suggestion pointed me in the right direction.

Appreciate the help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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