#1
|
|||
|
|||
creating a named row for dynamic addressing
Hi All,
I am currently using the code below to create various pages of user data, I am currently doing this by selecting the Column letter as a reference to the data I need EG name, date, serial number, etc... I would like to know how to insert columns in between these already set. All the data is in a table, how can I stop using the column header as a reference, and use the table column name in it's place, I can then insert extra column anywhere without having to rewrite my code each time. Code:
Sub button_click4() ' delivery docket If InRange(ActiveCell, Range("A4:A5000")) Then Sheets("Delivery Docket").Range("B13") = Range("i" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("B14") = Range("g" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("B15") = Range("h" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("C16") = Range("j" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("H13") = Range("i" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("H14") = Range("g" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("H15") = Range("h" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("I16") = Range("j" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("C22") = "Serial/C-bus # " & Range("e" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("C23") = "RMA # " & Range("a" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("J6") = Range("a" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("J8") = Range("a" & Mid(ActiveCell.Address, 4, 4)) Sheets("Delivery Docket").Range("J5") = Date Sheets("Delivery Docket").Range("J9") = Date Sheets("Delivery Docket").Select Exit Sub End If End Sub Sub button_click1() If InRange(ActiveCell, Range("A4:A5000")) Then Sheets("Payment Advice").Range("B3") = Date Sheets("Payment Advice").Range("B4") = Range("a" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B5") = Range("g" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B6") = Range("h" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B7") = Range("i" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B8") = Range("j" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B9") = Range("k" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B14") = Range("d" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B15") = Range("e" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B18") = Range("u" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B23") = Range("AC" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B24") = Range("AB" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B25") = Range("AD" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Range("B26") = Range("AE" & Mid(ActiveCell.Address, 4, 4)) Sheets("Payment Advice").Select Exit Sub End If End Sub Sub button_click2() If InRange(ActiveCell, Range("A4:A5000")) Then Sheets("Address Label").Range("C3") = Range("i" & Mid(ActiveCell.Address, 4, 4)) Sheets("Address Label").Range("C5") = Range("h" & Mid(ActiveCell.Address, 4, 4)) Sheets("Address Label").Range("C7") = Range("j" & Mid(ActiveCell.Address, 4, 4)) Sheets("Address Label").Range("C4") = Range("g" & Mid(ActiveCell.Address, 4, 4)) Sheets("Address Label").Range("C6") = Range("k" & Mid(ActiveCell.Address, 4, 4)) 'Sheets("Address Label").Range("B14") = Range("d" & Mid(ActiveCell.Address, 4, 4)) 'Sheets("Address Label").Range("B15") = Range("e" & Mid(ActiveCell.Address, 4, 4)) Sheets("Address Label").Select Exit Sub End If End Sub |
#2
|
||||
|
||||
First, the likes of:
Mid(ActiveCell.Address, 4, 4) can be more reliably written as: Activecell.row but you don't need it. 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)) Code:
Sheets("Address Label").Range("C5") = intersect(activecell.EntireRow,activecell.ListObject.ListColumns("hdr7").Range) Code:
Set tbl = ActiveCell.ListObject Sheets("Payment Advice").Range("B3") = Date Sheets("Address Label").Range("C5") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr1").Range) Sheets("Payment Advice").Range("B4") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr2").Range) Sheets("Payment Advice").Range("B5") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr3").Range) Sheets("Payment Advice").Range("B6") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr4").Range) There'll many ways, this one not necessarily the slickest. |
#3
|
||||
|
||||
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 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)) Code:
Sheets("Address Label").Range("C5") = intersect(activecell.EntireRow,activecell.ListObject.ListColumns("hdr7").Range) 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) There'll many ways, this one not necessarily the slickest. |
#4
|
|||
|
|||
That's perfect, just what I needed to make adding extra columns a breeze
|
#5
|
||||
|
||||
I've updated my response in my last message since you replied.
|
#6
|
|||
|
|||
Thanks for your help.
Is it also possible to put these 2 lines of code globally/public so I don't have to repeat them in all Sub's. Code:
Set tblColumns = ActiveCell.ListObject.ListColumns Set ACRow = ActiveCell.EntireRow |
#7
|
||||
|
||||
Sure but if you've got multiple tables you'll need to update tblcolmns anyway, and if the active cell is likely to move row, which by definition it is, your going to have to update that too.
|
#8
|
|||
|
|||
Hi,
Thanks for your help on this. I'll do it the way shown for now. Just one minor addition to my code... I know i can use vba to print a sheet, nut can I do that if the sheet is hidden. So instead of selecting the sheet ofter updating the fields, just print it. regards Trevor |
#9
|
||||
|
||||
If a sheet is hidden it has to be unhidden before printing, however briefly. This process can be largely hidden from the user with the likes of:
Code:
Application.ScreenUpdating = False With Sheets("Sheet1") originalVisibility = .Visible .Visible = xlSheetVisible .PrintOut .Visible = originalVisibility End With Application.ScreenUpdating = True |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating Dynamic Ranking Tables | ssol2016 | Excel | 1 | 10-27-2016 08:47 AM |
creating a code to create dynamic text in word | ah8471 | Word VBA | 1 | 12-29-2015 10:52 PM |
Creating a dynamic summary sheet | FenelonPaul | Excel | 5 | 09-23-2015 07:11 AM |
Help Creating A Dynamic Footer | Mikemo | Word | 3 | 02-05-2013 11:58 AM |
Dynamic Named Ranges using text | hannu | Excel | 0 | 06-22-2010 04:42 PM |