Thread: [Solved] creating a named row for dynamic addressing
View Single Post
 
Old 07-04-2018, 12:54 PM
trevorc trevorc is offline Windows 7 32bit Office 2013
Competent Performer
  
Join Date: Jan 2017
Posts: 173
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote