VBA - Exporting to Excel

[ClaireLou]

Hi
I have a master document that we're using to produce statements for Customers.

I need to pull out the various statements into other excel sheets but I've managed to write it as PDF and I cant get them to come out as excel files. I will also end up having to add an array to pull 2 tabs but I need the VBA to work first.

Am I doing something glaringly obviously wrong? I thought it was the fixed format but can't find what it should be. Code is below.

Thanks

Claire

Private Sub CommandButton1_Click()
On Error Resume Next
Dim i As Long
Dim sCount As String
Dim vSAMPLE1 As Variant
Dim SAMPLE2 As Variant
Dim isheet As Worksheet

Set isheet = ThisWorkbook.Sheets("Sample Sheet")
vSAMPLE1 = [CUSTOMERS]
i = UBound(vSAMPLE1)
If i > 1 Then
sCount = i & " client "
Else: sCount = i & " client "
End If

If MsgBox("A statement for " & sCount & "will be exported." _
& vbCr & "Do you want to continue?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"Sample Sheet") = vbYes Then
With Sheet2
For i = 1 To i
[CUSTOMERS] = vSAMPLE1(i, 1)
CUSTOMERS = [SAMPLE1] & ".xlsx"
ThisWorkbook.Sheets("Debt Statement").Select
ActiveSheet.ExportAsFixedFormat Type:=x1Normal, _
Filename:="C:\Users\MYFOLDER\MYFILES\" & CUSTOMERS, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next
End With
End If
MsgBox "Complete"
End Sub

 

[johnnyL]

FixedFormat