saschultz

New Member
Joined
Mar 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have created a proposal process tracking sheet for my team and have included an "add new client" button. The "add new client" button copies information from a maintenance tab and pastes it at an offset from a cell named "OneAbove" below the last entry on the Proposal Schedule.

VBA Code:
Sub InsertNewClientMacro()

On Error Resume Next

'Copy the NewClient range from the maintenance tab
    Range("NewClient").Copy

'Find cell OneAbove and Paste down the NewClient in OneAbove
    Range("OneAbove").Select
    Selection.Insert Shift:=xlDown
    
'Insert Data Validation in NewClient Assigned To Section
    Range("OneAbove").Resize(19, 1).Offset(-19, 2).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Maintenance!$C$2:$C$15"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
     End With

'Copy CalendarDrag from Maintenance and Paste to Proposal Schedule
    Range("CalendarDrag20").Copy Range("OneAbove").Offset(-20, 7)

Once the user has completed the relevant information, there is another button that calls a macro and adds 19 rows to a table on the Filtered Schedule tab called "ScheduleTable" along with copying and pasting the information from the newly added client on the Proposal Schedule tab.

VBA Code:
'Copy info to ScheduleTable
    Dim LastRow As Range
    Dim ScheduleTable As ListObject
    Dim FilteredSchedule As Worksheet
    Dim ProposalSchedule As Worksheet
    
    'Add row to bottom of Schedule Table
    Worksheets("Filtered Schedule").ListObjects("ScheduleTable").ListRows.Add
    
    Set ScheduleTable = Worksheets("Filtered Schedule").ListObjects("ScheduleTable")
    Set LastRow = ScheduleTable.ListRows(ScheduleTable.ListRows.Count).Range
    Set ProposalSchedule = Worksheets("Proposal Schedule")
    
    
    With LastRow
    
    'add rows & formulas
        .Cells(1, 1) = Range("OneAbove").Offset(-19, 2)
        .Cells(1, 2) = Range("OneAbove").Offset(-19)
        .Cells(1, 3) = Range("OneAbove").Offset(-20)
        .Cells(1, 4) = Range("OneAbove").Offset(-19, 4)
        
        .Cells(2, 1) = Range("OneAbove").Offset(-18, 2)
        .Cells(2, 2) = Range("OneAbove").Offset(-18)
        .Cells(2, 3) = Range("OneAbove").Offset(-20)
        .Cells(2, 4) = Range("OneAbove").Offset(-18, 4)
        
        .Cells(3, 1) = Range("OneAbove").Offset(-17, 2)
        .Cells(3, 2) = Range("OneAbove").Offset(-17)
        .Cells(3, 3) = Range("OneAbove").Offset(-20)
        .Cells(3, 4) = Range("OneAbove").Offset(-17, 4)
              
        .Cells(4, 1) = Range("OneAbove").Offset(-16, 2)
        .Cells(4, 2) = Range("OneAbove").Offset(-16)
        .Cells(4, 3) = Range("OneAbove").Offset(-20)
        .Cells(4, 4) = Range("OneAbove").Offset(-16, 4)
        
        .Cells(5, 1) = Range("OneAbove").Offset(-15, 2)
        .Cells(5, 2) = Range("OneAbove").Offset(-15)
        .Cells(5, 3) = Range("OneAbove").Offset(-20)
        .Cells(5, 4) = Range("OneAbove").Offset(-15, 4)
        
        .Cells(6, 1) = Range("OneAbove").Offset(-14, 2)
        .Cells(6, 2) = Range("OneAbove").Offset(-14)
        .Cells(6, 3) = Range("OneAbove").Offset(-20)
        .Cells(6, 4) = Range("OneAbove").Offset(-14, 4)
        
        .Cells(7, 1) = Range("OneAbove").Offset(-13, 2)
        .Cells(7, 2) = Range("OneAbove").Offset(-13)
        .Cells(7, 3) = Range("OneAbove").Offset(-20)
        .Cells(7, 4) = Range("OneAbove").Offset(-13, 4)
        
        .Cells(8, 1) = Range("OneAbove").Offset(-12, 2)
        .Cells(8, 2) = Range("OneAbove").Offset(-12)
        .Cells(8, 3) = Range("OneAbove").Offset(-20)
        .Cells(8, 4) = Range("OneAbove").Offset(-12, 4)
        
        .Cells(9, 1) = Range("OneAbove").Offset(-11, 2)
        .Cells(9, 2) = Range("OneAbove").Offset(-11)
        .Cells(9, 3) = Range("OneAbove").Offset(-20)
        .Cells(9, 4) = Range("OneAbove").Offset(-11, 4)
              
        .Cells(10, 1) = Range("OneAbove").Offset(-10, 2)
        .Cells(10, 2) = Range("OneAbove").Offset(-10)
        .Cells(10, 3) = Range("OneAbove").Offset(-20)
        .Cells(10, 4) = Range("OneAbove").Offset(-10, 4)
        
        .Cells(11, 1) = Range("OneAbove").Offset(-9, 2)
        .Cells(11, 2) = Range("OneAbove").Offset(-9)
        .Cells(11, 3) = Range("OneAbove").Offset(-20)
        .Cells(11, 4) = Range("OneAbove").Offset(-9, 4)
        
        .Cells(12, 1) = Range("OneAbove").Offset(-8, 2)
        .Cells(12, 2) = Range("OneAbove").Offset(-8)
        .Cells(12, 3) = Range("OneAbove").Offset(-20)
        .Cells(12, 4) = Range("OneAbove").Offset(-8, 4)
        
        .Cells(13, 1) = Range("OneAbove").Offset(-7, 2)
        .Cells(13, 2) = Range("OneAbove").Offset(-7)
        .Cells(13, 3) = Range("OneAbove").Offset(-20)
        .Cells(13, 4) = Range("OneAbove").Offset(-7, 4)
        
        .Cells(14, 1) = Range("OneAbove").Offset(-6, 2)
        .Cells(14, 2) = Range("OneAbove").Offset(-6)
        .Cells(14, 3) = Range("OneAbove").Offset(-20)
        .Cells(14, 4) = Range("OneAbove").Offset(-6, 4)
        
        .Cells(15, 1) = Range("OneAbove").Offset(-5, 2)
        .Cells(15, 2) = Range("OneAbove").Offset(-5)
        .Cells(15, 3) = Range("OneAbove").Offset(-20)
        .Cells(15, 4) = Range("OneAbove").Offset(-5, 4)
           
        .Cells(16, 1) = Range("OneAbove").Offset(-4, 2)
        .Cells(16, 2) = Range("OneAbove").Offset(-4)
        .Cells(16, 3) = Range("OneAbove").Offset(-20)
        .Cells(16, 4) = Range("OneAbove").Offset(-4, 4)
        
        .Cells(17, 1) = Range("OneAbove").Offset(-3, 2)
        .Cells(17, 2) = Range("OneAbove").Offset(-3)
        .Cells(17, 3) = Range("OneAbove").Offset(-20)
        .Cells(17, 4) = Range("OneAbove").Offset(-3, 4)
        
        .Cells(18, 1) = Range("OneAbove").Offset(-2, 2)
        .Cells(18, 2) = Range("OneAbove").Offset(-2)
        .Cells(18, 3) = Range("OneAbove").Offset(-20)
        .Cells(18, 4) = Range("OneAbove").Offset(-2, 4)
        
        .Cells(19, 1) = Range("OneAbove").Offset(-1, 2)
        .Cells(19, 2) = Range("OneAbove").Offset(-1)
        .Cells(19, 3) = Range("OneAbove").Offset(-20)
        .Cells(19, 4) = Range("OneAbove").Offset(-1, 4)

End With

'Add row to Client Drop Down options
  
'Add row to Client Drop Down options
    Dim LastRow2 As Range
    Dim Client As ListObject
    Dim Maintenance As Worksheet
    Dim ProposalSchedule As Worksheet
    
    'Add row to bottom of Client Table
    Worksheets("Maintenance").ListObjects("Client").ListRows.Add
    
    Set Client = Worksheets("Maintenance").ListObjects("Client")
    Set LastRow2 = Client.ListRows(Client.ListRows.Count).Range
    Set ProposalSchedule = Worksheets("Proposal Schedule")
    
    With LastRow2
        .Cells(1, 1) = Range("OneAbove").Offset(-20)
    End With

End Sub

Rather than having the user have to click the second button and manually update the table if changes are made, I would like to create a code that adds a formula to the new rows on the table so the new client information can be dynamic in the table.

Thank you!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,144,341
Messages
5,723,806
Members
422,518
Latest member
quack_quack

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