export to CSV file suddenly stops working

[exceltadpole]

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?

 

[Damon Ostrander]

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]

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.