Trying to make Macro to automatically fill cells and print

reptilematt

New Member
Joined
Nov 23, 2009
Messages
2
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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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:

Code:
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top