creating a named row for dynamic addressing

trevorc · #1 07-04-2018, 12:54 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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