Good Day to you all.
I am having an issue with the code below. I have spent hours trying to get it to run correctly but being a bit of a novice at coding I've hit a brick wall now. I would really appreciate your help.
The problem is if I run the code, then when asked I choose 'Yes' on the msgbox, the 'Price Builder' Workbook opens, becomes the active sheet and the fields update fine. However the value in F5 of the sourceSheet 'Add Customer' sheet will not update to the next five figure quote number as it should, it just returns a '1'. (That cell is formatted to be preceeded with a 'Q', therefore Q1).
If howerver I choose 'No' from the message box, which will update the database but not open 'Price Builder' workbook, Cell F5 of the 'Add Customer Sheet' updates fine.
I have had to blank out some of the link within the code for privacy reasons obviously.
Thank you in advance.
Regards
Nile.
I am having an issue with the code below. I have spent hours trying to get it to run correctly but being a bit of a novice at coding I've hit a brick wall now. I would really appreciate your help.
The problem is if I run the code, then when asked I choose 'Yes' on the msgbox, the 'Price Builder' Workbook opens, becomes the active sheet and the fields update fine. However the value in F5 of the sourceSheet 'Add Customer' sheet will not update to the next five figure quote number as it should, it just returns a '1'. (That cell is formatted to be preceeded with a 'Q', therefore Q1).
If howerver I choose 'No' from the message box, which will update the database but not open 'Price Builder' workbook, Cell F5 of the 'Add Customer Sheet' updates fine.
I have had to blank out some of the link within the code for privacy reasons obviously.
Thank you in advance.
Regards
Nile.
VBA Code:
Sub New_Customer_input_Data()
' Takes the information from the quote form and puts it in the quotation database after pressing the enter button
Dim sourceSheet As Worksheet
Dim dateSheet As Worksheet
Dim nextRow As Long
' Setting sheets for the macro to look for
Set sourceSheet = Sheet2 'Add customer Sheet
Set dataSheet = Sheet8 'DataBase sheet
' Get the next empty row from the database sheet
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
'Inputting the Add Customer(sourceSheet) values into the datasheet (DataBase)
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F5").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F9").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F11").Value
dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F13").Value
dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F15").Value
dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F17").Value
dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("F19").Value
dataSheet.Cells(nextRow, 10).Value = sourceSheet.Range("F21").Value
dataSheet.Cells(nextRow, 11).Value = sourceSheet.Range("F23").Value
dataSheet.Cells(nextRow, 12).Value = sourceSheet.Range("F25").Value
dataSheet.Cells(nextRow, 13).Value = sourceSheet.Range("J5").Value
dataSheet.Cells(nextRow, 14).Value = sourceSheet.Range("J7").Value
dataSheet.Cells(nextRow, 15).Value = sourceSheet.Range("J9").Value
dataSheet.Cells(nextRow, 16).Value = sourceSheet.Range("J11").Value
dataSheet.Cells(nextRow, 17).Value = sourceSheet.Range("J13").Value
dataSheet.Cells(nextRow, 18).Value = sourceSheet.Range("J15").Value
dataSheet.Cells(nextRow, 19).Value = sourceSheet.Range("J17").Value
dataSheet.Cells(nextRow, 20).Value = sourceSheet.Range("J19").Value
dataSheet.Cells(nextRow, 21).Value = sourceSheet.Range("J21").Value
dataSheet.Cells(nextRow, 22).Value = sourceSheet.Range("F28").Value
dataSheet.Cells(nextRow, 23).Value = sourceSheet.Range("F7").Value
dataSheet.Cells(nextRow, 24).Value = sourceSheet.Range("J23").Value
dataSheet.Cells(nextRow, 25).Value = sourceSheet.Range("J27").Value
'goto price builder workbook
If MsgBox("Continue onto the Price Builder?", vbYesNo, "Next Step") = vbYes Then
Workbooks.Open Filename:= _
"C:\Users\******************\Documents\************** ****************\ DATABASE FILES\Price Builder.xlsm"
'inputting data into price builder
Workbooks("Accounting Database.xlsm").Worksheets("Add Customer").Range("F5").Copy
Workbooks("Price Builder.xlsm").Worksheets("Annalysis").Range("C2").PasteSpecial Paste:=xlPasteValues
Workbooks("Price Builder.xlsm").Worksheets("Annalysis").Range("C3").Value = _
Workbooks("Accounting Database.xlsm").Worksheets("Add Customer").Range("F9").Value
Workbooks("Price Builder.xlsm").Worksheets("Annalysis").Range("C4").Value = _
Workbooks("Accounting Database.xlsm").Worksheets("Add Customer").Range("J7").Value
End If
'unprotect Add Customer sheet
sourceSheet.Unprotect
'Add next quote number to cell F5 in add customer
sourceSheet.Range("F5").Value = Range("F5").Count + 1
'Protect add Customer sheet
sourceSheet.Protect
'clearing date from Add Customer table
sourceSheet.Range("F7").Value = ""
sourceSheet.Range("F9").Value = ""
sourceSheet.Range("F11").Value = ""
sourceSheet.Range("F13").Value = ""
sourceSheet.Range("F15").Value = ""
sourceSheet.Range("F17").Value = ""
sourceSheet.Range("F19").Value = ""
sourceSheet.Range("F21").Value = ""
sourceSheet.Range("F23").Value = ""
sourceSheet.Range("F25").Value = ""
sourceSheet.Range("J5").Value = ""
sourceSheet.Range("J7").Value = ""
sourceSheet.Range("J9").Value = ""
sourceSheet.Range("J11").Value = ""
sourceSheet.Range("J13").Value = ""
sourceSheet.Range("J15").Value = ""
sourceSheet.Range("J17").Value = ""
sourceSheet.Range("J19").Value = ""
sourceSheet.Range("J21").Value = ""
sourceSheet.Range("J23").Value = ""
sourceSheet.Range("J27").Value = ""
End Sub