First, the likes of:
Mid(ActiveCell.Address, 4, 4)
can be more reliably written as:
Activecell.row
but you don't need it.
The rest of this suggestion depends on
Quote:
Originally Posted by trevorc
All the data is in a table,
|
meaning that the data is in a real Excel table (a
listobject in vba).
Let's say that column J on your sheet has the header "hdr7" for the table.
Then likes of:
Code:
Sheets("Address Label").Range("C5") = Range("j" & Mid(ActiveCell.Address, 4, 4))
can be:
Code:
Sheets("Address Label").Range("C5") = intersect(activecell.EntireRow,activecell.ListObject.ListColumns("hdr7").Range)
and since you'll be using
activecell.ListObject.ListColumns and
activecell.EntireRow on multiple lines you can make things easier with the likes of:
Code:
Set tblColumns = ActiveCell.ListObject.ListColumns
Set ACRow = ActiveCell.EntireRow
Sheets("Payment Advice").Range("B3") = Date
Sheets("Address Label").Range("C5") = Intersect(ACRow, tblColumns("hdr1").Range)
Sheets("Payment Advice").Range("B4") = Intersect(ACRow, tblColumns("hdr2").Range)
Sheets("Payment Advice").Range("B5") = Intersect(ACRow, tblColumns("hdr3").Range)
Sheets("Payment Advice").Range("B6") = Intersect(ACRow, tblColumns("hdr4").Range)
where I've invented imaginary header names
hdr1,
hdr2 etc.
There'll many ways, this one not necessarily the slickest.