Data not copying to register

[Jnb99]

Hi everyone,

I've compiled code which worked fine until this morning when I added the part to copy the main cells of the quote sheet to a Quote register.

What I am trying to achieve when macro is run is: Save active quote sheet as a new sheet with quote number as sheet name (WORKING), save a PDF copy with quote number as pdf file name (WORKING), copy main data from quote to register (NOT WORKING), Clear contents (WORKING).

Error info:
Run time error '1004'
Method 'range' of object '_worksheet' failed
Code in bold below is highlighted yellow

Thanks in advance

EDIT: Code is not showed as bold. I've added a comment

Code:

Private Sub CommandButton1_Click()
 
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Estimate")
Set WS2 = Worksheets("EDatabase")
' Figure out which row is next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important values to register
[B]WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("F4"), WS1.Range("F3"), WS1.Range("A14"), Range("EstTot"))[/B]                     'THIS IS THE ERROR

End Sub
 
Sub saveAsPdf()
Dim saveLocation As String
    Dim rng As Range
    saveLocation = "C:\Users\***\Estimates\" & Range("f4").Value & Range("a14").Value & ".pdf"
   
    Set rng = Worksheets("Estimate").Range("A1:g50")
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
   
    Call saveSheetWithoutFormulas
   
End Sub
Sub saveSheetWithoutFormulas()
    Dim ws As Worksheet
'f4 is document number
    Set wh = Worksheets(ActiveSheet.Name)
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wh.Range("f4").Value <> "" Then
    ActiveSheet.Name = wh.Range("f4").Value
    End If
    wh.Activate
With Sheets("Estimate").Range("f4")
     .Value = "E" & (Mid(.Value, 2) + 1)
End With
 Call clearContents
End Sub

Sub clearContents()
Range("a23:d43").clearContents
Range("f23:f43").clearContents
Range("a14").clearContents

End Sub

 

[Jnb99]

WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("F4"), WS1.Range("F3"), WS1.Range("A14"), Range("EstTot"))

This is the error

 

[dmt32]

Hi,
try this update to your line of code & see if resolves the issue

WS2.Cells(NextRow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)

Dave

 

[Jnb99]

Hi,
try this update to your line of code & see if resolves the issue

WS2.Cells(NextRow, 1).Resize(, 4).Value = Array(WS1.Range("F4").Value, WS1.Range("F3").Value _
                                               , WS1.Range("A14").Value, WS1.Range("EstTot").Value)

Dave

Hi Dave, nope same error

 

[dmt32]

Do you have another workbook open when running the code?

Dave

 

[Jnb99]

Do you have another workbook open when running the code?

Dave

No, that's the only workbook open

 

[dmt32]

Check the Name Manger & that the named range matches.

WS1.Range("EstTot")

Dave

 

[Jnb99]

Lol. How in the world did you know that. I did change it, but for some reason it changed back to G43. Ok now it is copying data, but the rest of the code is not working?

 

[Jnb99]

I've inserted "call saveAsPdf" and working again. Thank you Dave!

 

[dmt32]

I did not, just process of elimination - pleased you found resolution to rest of your issues

Dave