VBA Loops / IF

VBA Loops / IF



This is likely to be really simple for many, but I can't seem to get my head around it. I have the below which works almost perfectly.

I basically have a list of 50 invoices in the A column and a invoice template on another sheet. The invoice template will pull the information for A2 so all the information is in the invoice, then save. As the information is pulled only from A2, I need to then delete that row so it picks up the next item in the list.

Currently, because im deleted a row it is skipping numbers, for example, if I had a list of 1-10 it would save, 1,3,5,7,9 and complete.

Any help or edits would be appreciated

Sub test()
 For each x in range (“A2:A50”)
If x.value = “>1” then
 Sheets("Template Re-design").Select
 FileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
 "M:\2016\Collections AR\RESTORED\Invoices\" & Cells(1, 1).Value & ".pdf", _
 Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
 :=False, OpenAfterPublish:=False
 Sheets("Addresses").Select
 Rows("2:2").Select
 Selection.Delete Shift:=xlUp
 End if
 Next x
 End Sub

Edit: CODE tags added, thanks.

I'm a little confused by how your workbook actually works. If you delete row 2, then surely the cell references in the "Template Re-design" worksheet which refer to "Addresses" row 2 no longer work?

Perhaps you could attach a sample workbook.... Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I see.

In that case, try something like:

Sub foo()
    With Worksheets("Addresses")
        While Not .Cells(2, 1).Value = Empty
            Sheets("Template Re-design").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="M:\2016\Collections AR\RESTORED\Invoices\" & Cells(1, 1).Value & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            .Rows(2).EntireRow.Delete shift:=xlUp
        Wend
    End With
End Sub

Your step by step explanation is as I understood it.

Originally Posted by ddbroadhead92

It doesn't seem to like it.

Can you explain in more detail?! Is there an error (and if so, which error, on which line) ? Is there no error, but the result is not as expected?

Originally Posted by ddbroadhead92

As soon as I run it, it comes up with the following error

Run-time error
'-2147024773 (8007007b)':
document not saved

Try stepping through the code, using F8. Looks like it's failing on this line:

Sheets("Template Re-design").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="M:\2016\Collections AR\RESTORED\Invoices\" & Cells(1, 1).Value & ".pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False

Which was copied from your original macro...

Here's a sample attachment, which does exactly as you specified...

The only line I have modified for testing is:

                Filename:="C:\TEMP\" & .Cells(2, 1).Value & ".pdf", _

How it works?

The WHILE... WEND loop looks at cell "A2" on sheet addresses, and repeats the loop until that cell is empty.

The loop exports the invoice worksheet as PDF, then deletes row 2 of Addresses.

Make sense?!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. YUou can also 'Add Reputation' to those who have helped you. Thanks.