VBA Code Error '1004' Range Name Definition

[DMurray3]

Hi All,

Please forgive with my lack of expertise in VBA's....

I am having trouble getting a Range Name definition to work correctly; on evey attempt, I am getting '1004' Error...

The specific 'Range Name' syntax I am having trouble with, is one for which the "row-number" corresponds to the 'value' calculated in a specific cell of a worksheet.

'row number' value is in "Sheets("IngRTCompras").Range("H71")"​

while the Range Name definition I am writting is:

r = Sheets("IngRTCompras").Range("H71").Value​

and then

' Set DstRng2 = Sheets("bdATSC").Range(Cells(r, 2), Cells(r, 2))' (with-out the single-quotations..)....​

While trying to come up with the correct way to name the Range, in a plain and simple worksheet that same syntax shown above is accepted and works... I can't understand why it works in one workbook (sample) and not in another...

I'd appreciate your help on how to resolve the '1004 Error'?

For your reference, the full Macro I have spent hours working on is...

Sub CopyCellsIngRTATSC()


'This code will pick up a multiple ranges and add them to 4 separate sheets without
' selecting the data
'error handler- change Scooby_Doo to whatever you want


'On Error GoTo Scooby_Doo:
'unprotect all sheets
'Unprotect_All


'dim variables
    Dim DstRng As Range 'destination range = Sheets("bdATSRt").Range("tabATSRt")
    Dim DstRng1 As Range 'destination range =  Sheets("bdAsientos").Range("tabAsientos")
    Dim DstRng2 As Range 'destination range = Sheets("bdATSC").RangeRange(Cells(r,2), Cells(r,2))
 
    Dim SrcRng As Range 'source range Sheets("IngRTCompras").Range("load_IngRTCompras_ATSRt")
    Dim SrcRng1 As Range 'source range Sheets("IngRTCompras").Range("load_IngRTComprasAsientos")
    Dim SrcRng2 As Range 'source range Sheets("IngRTCompras").Range("load_IngRTATSC_Updated") this is one record/row


'dim of the exact column where we need to copy data from SrcRng2 = Sheets("IngRTCompras").Range("load_IngRTATSC_Updated")
    Dim r As Long 'to define the specific row number we need to copy to DstRng2 = Sheets("bdATSC").RangeRange(Cells(r,2), Cells(r,2))


'destination variable
    Set DstRng = Sheets("bdATSRt").Range("b2")
    Set DstRng1 = Sheets("bdAsientos").Range("b2")


'*r* is the *row number* needed to set DstRng = Sheets("bdATSC").RangeRange(Cells(r,2), Cells(r,2))


    r = Sheets("IngRTCompras").Range("H71").Value


'substituting r's value and use in DstRng2 to obtain the full cell address in Sheets("bdATSC") where we need to copy our SrcRng2
'****THIS IS THE STEP THAT THROWS A Run-time error '1004' Application-defined or Object-defined error ERROR ****


    Set DstRng2 = Sheets("bdATSC").Range(Cells(r, 2), Cells(r, 2))


'hold in memory
    Application.ScreenUpdating = False


'mandatory field validation
    If Range("J3") <> "" Then
        MsgBox "No ha seleccionado la EMPRESA. Revisar !"
        Exit Sub
    ElseIf Range("J4") <> "" Then
        MsgBox "No ha seleccionado el PROVEEDOR. Revisar !"
        Exit Sub
    ElseIf Range("J5") <> "" Then
        MsgBox "Ha omitido o hay error en # de FACTURA. Revisar !"
        Exit Sub
    ElseIf Range("K8") <> "" Then
        MsgBox "Error de BASE en FACTURA ORIGEN. Revisar Tablas. Revisar !"
        Exit Sub
    ElseIf Range("J15") <> "" Then
        MsgBox "Ha omitido NRO RETENCION. Revisar !"
        Exit Sub
    ElseIf Range("J16") <> "" Then
        MsgBox "Formulario Retención NO APROBADO. Revisar !"
        Exit Sub
    ElseIf Range("J18") <> "" Then
        MsgBox "Error u omisión FECHA EMISION RETENCION. Revisar !"
        Exit Sub
    ElseIf Range("L20") <> "" Then
        MsgBox "Error de BASE en INFO PROVEEDOR. Revisar Tablas. Revisar !"
        Exit Sub
    ElseIf Range("K26") <> "" Then
        MsgBox "Ha omitido el CONCEPTO de la Retención. Revisar !"
        Exit Sub
    ElseIf Range("L28") <> "" Then
        MsgBox "Error de BASE en INFO ATSC. Revisar Tablas. Revisar !"
        Exit Sub
    ElseIf Range("L32") <> "" Then
        MsgBox "Error VALOR TOTAL RETENCION. Revisar !"
        Exit Sub
    'give the user a chance to exit here
        Select Case MsgBox _
        ("Está por actualizar esta Retención." _
        & vbCrLf & "Verifique que todo se encuentre el orden antes de proceder.", _
        vbYesNo Or vbExclamation, "Todo Ok?")
        Case vbYes
        Case vbNo
        Exit Sub
        End Select
    End If


'***copy and paste data without selecting
'**first sheet
' reset and sort destination table
    'Sort_bdATSRt sub not defined yet
    
'source variable
   Set SrcRng = Sheets("IngRTCompras").Range("load_IngRTCompras_ATSRt")
   SrcRng.Copy
   DstRng.End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues


' reset and sort desitination table after data load
   'Sort_bdATSRt sub not defined yet
    
'**second sheet
' reset and sort desitination table
   Sort_bdAsientos


'source variable
   Set SrcRng1 = Sheets("IngRTCompras").Range("load_IngRTComprasAsientos")
   SrcRng1.Copy
   DstRng1.End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues


' reset and sort desitination table after data load
   Sort_bdAsientos
    
'**third sheet
' reset and sort desitination table
    Sort_bdATSC


'source variable
    Set SrcRng2 = Sheets("IngRTCompras").Range("load_IngRTATSC_Updated")
    SrcRng2.Copy
    DstRng2.PasteSpecial xlPasteValues


' reset and sort desitination table after data load
    Sort_bdATSC


'empty clipboard
    Application.CutCopyMode = False


'confirmation message
    MsgBox "El Comprobante de Retención ha sido actualizado" _
    & vbCrLf & "correctamente en las bases de datos"


'clear the invoice
    'LimpiarIngRTATSC


End Sub

The structure of the above Macro follows a basic construct I have been able to build-up from snippets and examples gathered from a number of links and sites and -except of the special 'Range Name' case in this Code- all other work perfectly.

Again, Many thanks for your help and continued support.

Kind regards,

Daniel Murray
DMurray3

 

[AlphaFrog]

Thy this...
Set DstRng2 = Range(Sheets("bdATS.C").Cells(r, 2), Sheets("bdATS.C").Cells(r, 2))

Or better yet, just this...
Set DstRng2 = Sheets("bdATSC").Cells(r, 2)

 

[DMurray3]

Many thanks AlphaFrog... The second alternative worked perfectly...

Problem solved.

 

[AlphaFrog]

You're welcome.