Trying to make Macro to automatically fill cells and print

[reptilematt]

Revised title: "Trying to make Macro to automatically fill cells from seperate column, print, then loop with next consecutive values from seperate column."

Hi,

I have an "automated" billing system that I made with =INDEX and MATCH functions.

There are 4 spreadsheets:

1. "Bill"
2. "Tenants"
3. "Electricity
4. "Graph"

Sheet 1 "Bill" has 2 sperate bills per page with an input box for each bill that allows the user to type in a specific apartment number and have the system fill the rest of the information (ex. names, electricity use, price, etc).

I'm trying to write a Macro to have the computer automatically fill the two cells, PRINT, then loop to fill the cells with the next consecutive values from a seperate column/table.

Is something like this possible without having to use the Record mechanism? I can do it with Record, but I have to go through 300 or more bills!

Any help would be appreciated.

 

[Kernkraft]

bumping this because I'm searching for something similar

 

[reptilematt]

bumping this because I'm searching for something similar

I actually have the solution for this. A member from the forum generously helped me with a solution. I will send you more details tonight.

 

[Smitty]

Welcome to the Board!

I actually just did this for someone on my blog a few minutes ago:

Yes, you can do that. You’ll need some code to loop through your validation list, populate your validation cell and print. Here’s an example where the list is in column A on sheet 2 and the validation cell is A1 on sheet 1...

The code loops through each value in the list, puts it in A1 and shows PrintPreview. Just change the sheet and range references to suit:

Sub PrintValidation()
  Dim i As Long
    For i = 1 To Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
      With Sheets("Sheet1")
        .Range("A1").Value = Sheets("Sheet2").Cells(i, "A").Value
        .PrintPreview
      End With
    Next i
End Sub

Hope that helps,