Macro Help

[SJP040473]

I have the following Macro which generates multiple invoices and then creates a PDF for each one. Is it possible for it to generate them as separate excel sheets instead?

Sub Invoice()
Dim Month As String, FltRng As Range, cell As Range
Application.ScreenUpdating = False
With Sheet4
    Month = .Range("D2")
    With .Range("A4:E" & .Cells(Rows.Count, "A").End(xlUp).Row)
        .AutoFilter 5, Month
    End With
    Set FltRng = .Range("A6", .Range("A6").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
    .AutoFilterMode = False
End With
For Each cell In FltRng
    With Sheet2
 .Range("B16:D31").ClearContents
         If cell = cell.Offset(-1, 0) Then GoTo nxt
        .Range("D3") = cell.Offset(, 7)
        .Range("D4") = Date
        .Range("B8") = cell.Offset(, 1)
        .Range("B14") = cell.Offset(, 2)
        .Range("B16") = cell
        .Range("D16") = cell.Offset(, 5)
        If cell.Offset(1, 0) = cell Then
            .Range("D3") = .Range("D3") & " AND " & cell.Offset(1, 7)
            .Range("D16") = .Range("D16") + cell.Offset(1, 6)
        End If
    End With
    Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & cell.Offset(, 1) & " " & cell
nxt:
Next cell
Application.ScreenUpdating = True
End Sub

 

[danners430]

To create the new sheet, all you need is similar code to this:

    With ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        .Name = sheetName
    End With

then all you need to do is input the correct data into the correct cells.

Alternatively, you could create a template sheet, and change the above code to:

    With ThisWorkbook.Sheets(sheetname).Copy(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        .Name = sheetName
    End With

and again, simply input the relevant data into the relevant cells.