Absolute Reference with VBA to database

[jerrystuart16]

Hello

In my example, I have a list of fields (name, address, phone, etc.) that I transfer from an inputs worksheet and use VBA button to save it to another sheet for the database reference.

So, it starts at J5.
Then it is on other columns on the sheets.

And there are a few locations for inputs in different columns and rows on this sheet.

But if I add rows or columns and move the worksheet where the source data is from, then it doesn't get transferred over, because the cell references have changed.

Is there a solution for this?

Here is my VBA code:

--

Private Sub Button29_Click()
erw = Sheet10.Cells(1, 1).CurrentRegion.Rows.Count + 1


If Len(Range("J5")) <> 0 Then
Sheet10.Cells(erw, 1) = Range("J5")
Sheet10.Cells(erw, 2) = Range("J6")
Sheet10.Cells(erw, 3) = Range("J7")
Sheet10.Cells(erw, 4) = Range("J8")
Sheet10.Cells(erw, 5) = Range("J9")
Sheet10.Cells(erw, 6) = Range("J10")
Sheet10.Cells(erw, 7) = Range("J11")
Sheet10.Cells(erw, 8) = Range("J12")
Sheet10.Cells(erw, 9) = Range("J13")
Sheet10.Cells(erw, 10) = Range("J14")
Sheet10.Cells(erw, 11) = Range("J16")
Sheet10.Cells(erw, 12) = Range("J15")
Sheet10.Cells(erw, 13) = Range("C9")
Sheet10.Cells(erw, 14) = Range("C25")
Sheet10.Cells(erw, 15) = Range("C19")
Sheet10.Cells(erw, 16) = Range("C20")
Sheet10.Cells(erw, 17) = Range("C21")
Sheet10.Cells(erw, 18) = Range("C22")
Sheet10.Cells(erw, 19) = Range("C31")
Sheet10.Cells(erw, 20) = Range("F33")
Sheet10.Cells(erw, 21) = Range("K27")
Sheet10.Cells(erw, 22) = Range("K28")
Sheet10.Cells(erw, 23) = Range("K29")
Sheet10.Cells(erw, 24) = Range("K30")
Sheet10.Cells(erw, 25) = Range("K31")
Sheet10.Cells(erw, 26) = Range("K32")
Sheet10.Cells(erw, 27) = Range("G40")
Sheet10.Cells(erw, 28) = Range("G38")
Sheet10.Cells(erw, 29) = Range("G39")
Sheet10.Cells(erw, 30) = Range("C71")
Sheet10.Cells(erw, 31) = Range("K53")
Sheet10.Cells(erw, 32) = Range("K60")
Sheet10.Cells(erw, 33) = Range("K13")












Else:
MsgBox "You must enter a name and contact info"
End If

--

Thanks!

 

[dmt32]

Is Sheet10 the Tab Name or the Sheets Code Name?

code provided refers to sheets TAB name.

Dave

 

[jerrystuart16]

Hi Dave

Tried this both ways; sheet # and named sheets.
Removed the ClientData-E to ClientDataE and still was unsuccessful?

Getting error here:

For Each wsDatabase In ThisWorkbook.Worksheets(Array("ClientData-E", "ClientData-F"))

--

Full look below:

--

Private Sub Button29_Click()
Dim DataEntryRange As Range, Cell As Range
Dim wsDatabase As Worksheet
Dim DataArray() As Variant
Dim i As Integer
Dim erw As Long

'database worksheet
Set wsDatabase = Sheet10
'data entry input range
Set DataEntryRange = ThisWorkbook.Worksheets("ScoreCard-Estimated").Range("J5:J14,J16:J15,C9,C25,C19:C22,C31,F33," & _
"K27:K32,G40,G38:G39,C71,K53,K60,K13")
'check entry in J5
If Len(DataEntryRange.Cells(1, 1).Value) = 0 Then
MsgBox "You must enter a name and contact info", 48, "Entry Required"
Else
'size array
ReDim DataArray(1 To DataEntryRange.Cells.Count)
'populate array elements
For Each Cell In DataEntryRange.Cells
'array element counter
i = i + 1
DataArray(i) = Cell.Value
Next Cell
'apply array to tables
For Each wsDatabase In ThisWorkbook.Worksheets(Array("ClientData-E", "ClientData-F"))
With wsDatabase
erw = .Cells(1, 1).CurrentRegion.Rows.Count + 1
.Cells(erw, 1).Resize(1, UBound(DataArray)) = DataArray
End With
Next wsDatabase
End If
End Sub

 

[dmt32]

Hi,
Ran the code without errors – suggest that you check your sheet names match those specified in your code.
Also, little puzzled, I thought you had replaced reference to the hard coded ranges with the name manager (shown in red)

Dave

Sub Button29_Click()
    Dim DataEntryRange As Range, Cell As Range
    Dim wsDatabase As Worksheet
    Dim DataArray() As Variant
    Dim i As Integer
    Dim erw As Long
    


'data entry input range
    Set DataEntryRange = ThisWorkbook.Worksheets("ScoreCard-Estimated").Range("J5:J14,J16:J15,C9,C25,C19:C22,C31,F33," & _
                                                                                "K27:K32,G40,G38:G39,C71,K53,K60,K13")
'data entry input range from name manager
    'Set DataEntryRange = ThisWorkbook.Worksheets("ScoreCard-Estimated").Range("DataEntryNameRange")
                                                                    
'check entry in J5
    If Len(DataEntryRange.Cells(1, 1).Value) = 0 Then
        MsgBox "You must enter a name and contact info", 48, "Entry Required"
    Else
'size array
        ReDim DataArray(1 To DataEntryRange.Cells.Count)
'populate array elements
        For Each Cell In DataEntryRange.Cells
'array element counter
            i = i + 1
            Cell.Value = i
            DataArray(i) = Cell.Value
        Next Cell
'apply array to tables
    For Each wsDatabase In ThisWorkbook.Worksheets(Array("ClientData-E", "ClientData-F"))
        With wsDatabase
            erw = .Cells(1, 1).CurrentRegion.Rows.Count + 1
            .Cells(erw, 1).Resize(1, UBound(DataArray)) = DataArray
        End With
    Next wsDatabase
    
    End If
End Sub