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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,943
Messages
5,856,438
Members
431,814
Latest member
qualitypavingstone

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