Cell value update issue after running VBA code

NileUk

New Member
Joined
Jan 20, 2021
Messages
10
Office Version
  1. 2007
Platform
  1. Windows
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.

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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try it like
VBA Code:
sourceSheet.Range("F5").Value = sourceSheet.Range("F5").Count + 1
 
Upvote 0
Solution
Try it like
VBA Code:
sourceSheet.Range("F5").Value = sourceSheet.Range("F5").Count + 1
Wow. Thank you. Why couldn't i see that. I also changed count to value. Works Fine now. Much appreciated.

VBA Code:
sourceSheet.Range("F5").Value = sourceSheet.Range("F5").Value + 1
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

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