Add Data Across multiple sheets sorted by date

amoverton2

Board Regular
Joined
May 13, 2021
Messages
77
Office Version
  1. 2016
Platform
  1. Windows
Hi All!

So this is a want more than a need but I'm new to VBA and figured maybe there is a wizard on here that might know how to do this... if it can't be done that is cool too, I'll keep doing it manually...

I have a workbook with several sheets acting as a data base that another sheet pulls all the data from those sheets to make a nice tracker. (Eventually, I'd like to separate the database sheets into another workbook for other reasons and another forthcoming post).

I have a multi-page userform where I can add data to all of those sheets on the next available row on every sheet at the same time.

The main sheet tracker is sorted by the date (oldest to newest with today as the oldest as most dates are in the future), not auto filtered since I brought over existing data that was already sorted by date (that
workbook didn't have database sheets).

On the multi-page userform there is a textbox where a date is entered, usually a date in the future (me.txtEDA.value) that goes on sheet "E_ProspectiveGain_Add", column e.

I would like when I click "Save" for excel to look at the date from the textbox (me.txtEDA.value) and compare it with the list of dates on the sheet E_ProspectiveGain_Add/column e, then add a row below the date where it is chronologically appropriate and add all information from the multi-page userform across all of the sheets at the same time (or in order of the vba code).

For example: The date entered (1MAR22) should trigger adding a row below row 5 on every sheet and the information from the multi-page userform is added to row 5 (the former row 6 is now row 7 and so on).

Date in textbox:
1MAR22

List of Dates:
1: 15JAN22
2: 24JAN22
3: 4FEB22
4: 18FEB22
5: 25FEB22
6: 4MAR22
7: 10MAR22
8: 31MAR22
9: 6APR22
10: 15APR22

Here is the code for the current add (Save) button.

VBA Code:
Private Sub cmdSAVE_Click()

    Dim mpPGI As Long, mpAI As Long, mpTI As Long, mpFI As Long, mpMN As Long, mpAS As Long, mpLC As Long

    mpPGI = ThisWorkbook.Sheets("E_ProspectiveGain_Add").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "C").Value = Me.txtRATE.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "D").Value = Me.cmbUIC.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "E").Value = Me.txtEDA.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "F").Value = Me.txtCPHONE.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "G").Value = Me.txtWPHONE.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "H").Value = Me.txtPEMAIL.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "I").Value = Me.txtWEMAIL.Value
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "J").Value = Application.UserName
    Sheets("E_ProspectiveGain_Add").Cells(mpPGI + 1, "K").Value = Now

    mpAI = ThisWorkbook.Sheets("E_AdminInfoGain_Add").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "C").Value = Me.txtBSC.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "D").Value = Me.txtBBDCODE.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "E").Value = Me.txtRELRATE.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "F").Value = Me.txtRELNAME.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "G").Value = Me.txtDETACHCMD.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "H").Value = Me.txtEDD.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "I").Value = Me.txtADD.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "J").Value = Me.cmbOPHOLD.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "K").Value = Me.cmbORDMOD.Value
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "L").Value = Application.UserName
    Sheets("E_AdminInfoGain_Add").Cells(mpPGI + 1, "M").Value = Now

    mpTI = ThisWorkbook.Sheets("E_TravelInfo_Add").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "C").Value = Me.cmbLOCAL.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "D").Value = Me.cmbARRISLAND.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "E").Value = Me.txtDEPARTCTY.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "F").Value = Me.txtFLTINFO.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "G").Value = Me.txtFLTDATE.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "H").Value = Me.txtLANDTIME.Value
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "I").Value = Application.UserName
    Sheets("E_TravelInfo_Add").Cells(mpPGI + 1, "J").Value = Now

    mpFI = ThisWorkbook.Sheets("E_FamilyInfo_Add").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "C").Value = Me.cmbSPOUSE.Value
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "D").Value = Me.cmbKIDS.Value
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "E").Value = Me.cmbPETS.Value
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "F").Value = Application.UserName
    Sheets("E_FamilyInfo_Add").Cells(mpPGI + 1, "G").Value = Now

    mpMN = ThisWorkbook.Sheets("E_MiscNotes_Add").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "A").Value = "=Row()-1"
    Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "B").Value = Me.txtPGNAME.Value
    Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "C").Value = Me.txtMISCNOTES.Value
    Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "D").Value = Application.UserName
    Sheets("E_MiscNotes_Add").Cells(mpPGI + 1, "E").Value = Now

    MsgBox "Information Added"

    ThisWorkbook.Save
    MsgBox "Information Saved"
    
    Call Reset

End Sub

Thanks!!
 
IDProspective GainRateUICEstimated Date of ArrivalCell Phone #Work Phone #Personal EmailWork EmailSubmitted ByUpdated
1COWELL,SIMONEMN21234517-Nov-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
2PRATT,CHRISEN11234530-Nov-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
3EVANS,CHRISPS25432114-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
4ABDUL,PAULAEMN16789014-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
5KLUM,HEIDIMMN11234515-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
6CLINTON,HILARYET25432115-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
7JORDAN,MICHAELGSM21234515-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
8MCDONALD,NORMGSM25432115-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
9YELTSIN,BORISETV26789015-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
10MANNING,ELIFCC5432120-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
11TREBEK,ALEXBM25432127-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
12THATCHER,MARGEMM26789030-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
13Prospective Gain NameProspective Gain RateUIC7-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:34:19 AM
14KELLY,JIMEM15432112-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
15SPENCER,DIANAMMA26789012-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
16LINCOLN,ABEGSMC5432113-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
1 nov, 1 dec, 1 jan, 1 feb, 1 mar

IDProspective GainRateUICEstimated Date of ArrivalCell Phone #Work Phone #Personal EmailWork EmailSubmitted ByUpdated
1Prospective Gain NameProspective Gain RateUIC1-Nov-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:51:58 AM
2COWELL,SIMONEMN21234517-Nov-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
3PRATT,CHRISEN11234530-Nov-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
4Prospective Gain NameProspective Gain RateUIC1-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:39:42 AM
5Prospective Gain NameProspective Gain RateUIC1-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:52:18 AM
6EVANS,CHRISPS25432114-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
7ABDUL,PAULAEMN16789014-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
8KLUM,HEIDIMMN11234515-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
9CLINTON,HILARYET25432115-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
10JORDAN,MICHAELGSM21234515-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
11MCDONALD,NORMGSM25432115-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
12YELTSIN,BORISETV26789015-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
13MANNING,ELIFCC5432120-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
14TREBEK,ALEXBM25432127-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
15THATCHER,MARGEMM26789030-Dec-2021Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
16Prospective Gain NameProspective Gain RateUIC1-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:52:40 AM
17Prospective Gain NameProspective Gain RateUIC7-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:34:19 AM
18KELLY,JIMEM15432112-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
19SPENCER,DIANAMMA26789012-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
20LINCOLN,ABEGSMC5432113-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
21SUAVE,RICOFCC5432114-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
22OBAMA,BARRACKMMN15432115-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
23FARVE,BRETTFCCS5432117-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
24HOOVER,EDGARCMDCM6789020-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
25ROOSEVELT,TEDDYHN6789020-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
26WASHINGTON,GEORGEND26789020-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
27BONAPARTE,NAPOLEANEMCS1234520-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
28WALSH,STEVEFC25432120-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
29KENNEDY,JOHNMMN15432121-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
30SANDERS,DEONEMN26789027-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
31ROOSEVELT,FRANKMMN25432128-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
32THOMAS,THURMANND21234530-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
33BUSH,GEORGEND35432130-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
34MONTANA,JOEHT15432131-Jan-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
35Prospective Gain NameProspective Gain RateUIC1-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:40:03 AM
36Prospective Gain NameProspective Gain RateUIC1-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:53:02 AM
37CLINTON,BILLHT1123458-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
38POOH,WINNIEETVCS5432111-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
39BUNNY,BUGSMMN15432114-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
40HELMSWORTH,CHRISMM15432115-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
41RODGERS,AARONTM26789015-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
42DILLON,BOBETN15432116-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
43JAMES,LEBRONMMA21234516-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
44PUTIN,VLADMMA25432116-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
45BISMARCK,OTTOMMN15432116-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
46MANNING,PEYTONMM21234516-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
47Prospective Gain NameProspective Gain RateUIC16-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:41:01 AM
48JACKSON,MICHAELEN21234517-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
49BRADY,TOMDC26789017-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
50DUCK,DAFFYFC26789017-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
51CAREY,MARIAHTM16789017-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
52JACKSON,BOETN16789017-Feb-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressOverton, Adam23-dec-21 10:35:00 AM
53Prospective Gain NameProspective Gain RateUIC1-maa-2022Cell Phone NumberWork Phone NumberPersonal Email AddressWork Email AddressBSA7-jan-22 7:53:22 AM
 
Upvote 0
in my previous macro, i added 1 line with a msgbox, so that you can see the date VBA uses and the date at the line where 'll be inserted
So can you copy this line also to your actual macro.
I tried to give it a color, unsuccesfull, but it's that part inbetween [COLOR] and [/COLOR]

I used everytime the icon for the datepicker at the right, did you use that one to ?

VBA Code:
Private Sub cmdSAVE_Click()

     Dim mpPGI As Long, mpAI As Long, mpTI As Long, mpFI As Long, mpMN As Long, mpAS As Long, mpLC As Long


     With ThisWorkbook.Sheets("E_ProspectiveGain_Add")          'this sheet
          ThisWorkbook.Names.Add "mIjnDatum", DateValue(Me.txtEDA.Value)     'defined name "MijnDatum" for the date in txtEDA
          Set c = .Range("A1").CurrentRegion.Columns("E")       'range with the dates in column E
          c.Name = "MijnDatums"                                 'defined name "MijnDatums"
          a = [transpose((mijndatums>mijndatum)*isnumber(mijndatums))]     'mark in an array all the dates>mijndatum as 1
          rij = Application.Match(1, a, 0)                      'row with the 1st 1, thus 1st date greater then MijnDatum
          If Not IsNumeric(rij) Then rij = UBound(a) + 1        'no row find, so next row
    
     [COLOR=rgb(184, 49, 47)]     MsgBox "txtEDA : " & Me.txtEDA.Value & vbLf & "used date : " & Format(DateValue(Me.txtEDA.Value), "dd/mmm/yy") & vbLf & "date before " & Format(c.Cells(rij).Value, "dd/mmm/yy")
          [/COLOR]
          c.Cells(rij).EntireRow.Insert                         'insert a row there
          With c.Cells(rij, 2 - c.Column)                       'in the inserted row
               .Offset(1).Resize(, 11).Copy .Offset(0)          'copy format of next row to this new inserted row
               .Formula = "=ROW()-1"                            '1st cell formule
               .Offset(, 1).Resize(, 10).Value = Array(Me.txtPGNAME.Value, Me.txtRATE.Value, Me.cmbUIC.Value, Me.txtEDA.Value, Me.txtCPHONE.Value, Me.txtWPHONE.Value, Me.txtPEMAIL.Value, Me.txtWEMAIL.Value, Application.UserName, Now)     'add your stuff in 1 line
          End With
     End With
 
Last edited:
Upvote 0
i saw 1 problem with 1 mar, that's was a string, so when writing the data to the sheet, you must use DateValue(Me.txtEDA.Value)
VBA Code:
replace
.Offset(, 1).Resize(, 10).Value = Array(Me.txtPGNAME.Value, Me.txtRATE.Value, Me.cmbUIC.Value, Me.txtEDA.Value, Me.txtCPHONE.Value, Me.txtWPHONE.Value, Me.txtPEMAIL.Value, Me.txtWEMAIL.Value, Application.UserName, Now)     'add your stuff in 1 line
by
.Offset(, 1).Resize(, 10).Value = Array(Me.txtPGNAME.Value, Me.txtRATE.Value, Me.cmbUIC.Value, DateValue(Me.txtEDA.Value), Me.txtCPHONE.Value, Me.txtWPHONE.Value, Me.txtPEMAIL.Value, Me.txtWEMAIL.Value, Application.UserName, Now)     'add your stuff in 1 line
 
Upvote 0
That's awesome! Quick question, on the other sheets where is the rest of the information from the multi-page, is it on the same row as E_ProspectiveGain_Add? Would you upload the workbook with the code you used?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top