runtime error 1004

[mellows23]

Hi There,

I am getting a runtime error on the following?

It basically puts some data into a couple of tabs, then saves those two tabs as a pdf. Inputs new data and creates a pdf again in a loop. There are about 25 iterations of this required. But after somewhere between 5-7 I get a runtime error.

Any ideas?

Code below;



Private Sub CommandButton1_Click()

'copy and paste onto po sheet

Dim A As Integer
Dim B As Integer
Dim FolderPath As String

'capture no. of iterations

B = Sheet1.Range("iterations").Value

'copy and paste data

For A = 1 To B

Sheet3.Range("a8") = Sheet1.Range("G1").Offset(A, 0)
Sheet2.Range("b2") = Sheet1.Range("c1").Offset(A, 0)
Sheet2.Range("b3") = Sheet1.Range("H1").Offset(A, 0)
Sheet2.Range("b6") = Sheet1.Range("d1").Offset(A, 0)
Sheet2.Range("h1") = Sheet1.Range("e1").Offset(A, 0)
Sheet3.Range("a9") = Sheet1.Range("f1").Offset(A, 0)
Sheet3.Range("k29") = Sheet1.Range("I1").Offset(A, 0)

FolderPath = "C:\Users\amellows\Desktop"

Dir FolderPath

Sheets(Array("Project details", "cost breakdown")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & Sheet1.Range("J1").Offset(A, 0), _
openafterpublish:=True, ignoreprintareas:=False

Next





End Sub

 

[pbcnick]

Sheets(Array("Project details", "cost breakdown")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & Sheet1.Range("J1").Offset(A, 0), _
openafterpublish:=True, ignoreprintareas:=False

I noticed you are opening each file after creation? perhaps the bolded filename you are saving as is the same and the file is open and therefore locked?
just a thought. if so, insert your variable 'A' into the filename, along with an undersoce.. e.g. Filename:=FolderPath & A & "_" & Sheet1.Range("J1").Offset(A, 0),
Also, I see FolderPath doesn't end with a backslash, does the value in Sheet1.Range("J1").Offset(A,0) start with a backslash?

HTH

 

[mellows23]

I noticed you are opening each file after creation? perhaps the bolded filename you are saving as is the same and the file is open and therefore locked?
just a thought. if so, insert your variable 'A' into the filename, along with an undersoce.. e.g. Filename:=FolderPath & A & "_" & Sheet1.Range("J1").Offset(A, 0),
Also, I see FolderPath doesn't end with a backslash, does the value in Sheet1.Range("J1").Offset(A,0) start with a backslash?

HTH

Seems to be working now, thanks for your help.