How to copy a value to last row of named range in a different worksheet.

[strong100m]

I am trying to create a VBA to copy a value from an specific cell and worksheet (example: cell A1 in worksheet "Info"), to the first available row in a specific named range ("test") in worksheet "Data".

So far I have gotten this but it doesn't seems to work:

Sub SaveData()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long
Set ws1 = Sheets("Info")
Set ws3 = Sheets("Data")
LastRow = ws2.Range("Test").Rows.Count + 1
ws1.Range("A1").Copy ws3.Range("Test" & LastRow)

End Sub

Can someone please help me with this code?

 

[Joe4]

If you just wanted to paste the value from cell A1 on your Info sheet to the first available cell in a particular column on your Data sheet, you can simplify it a little like this:

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyCol As String


    Set ws1 = Sheets("Info")
    Set ws2 = Sheets("Data")
    
    '   Specify column to copy to on ws2
    copyCol = "A"
    
    ws1.Range("A1").Copy ws2.Cells(Rows.Count, copyCol).End(xlUp).Offset(1, 0)

If you had wanted to do a whole bunch of columns and wanted to loop through them, you could change copyCol to a numeric field, as in Cells(row,column), your column reference can be a letter or a number (i.e. "A"=1, "B"=2, etc).

So
Cells(Rows.Count,"A")
is the same as
Cells(Rows.Count,1)

 

[strong100m]

I understand your point of view and the flow of this code.

Now, Is it possible to involved the named range somewhere in the code directly?

Otherwise I could use a HUGE "if statement" like the one shown below in order to allocate the data under the proper column.

Dim ws1 As Worksheet, ws2 As Worksheet
Dim Lookup As String

Set ws1 = Sheets("Info")
Set ws2 = Sheets("Data")

'Obtain named of named range from concatenated drop down list values
'A6 is the cell where all the values from the drop down list are concatenated
Named_range= A6
If
Named_range=2015NameAgeGender
Then
Lookup = A
'Where A is the column assigned to the named range "2015NameAgeGender"
Else If
Named_range=2015NameAgeGender
Then
Lookup = B
.
.
.
Otherwise...

'Use the assigned value of Lookup to record the value on ws2

ws1.Range("A1").Copy ws2.Cells(Rows.Count, Lookkup).End(xlUp).Offset(1, 0)

 

[strong100m]

Under the "Else If" statement it should say: Named_range=2016NameAgeGender

I apologize for the typo

 

[Joe4]

I'm a little confused as to the necessity/reason for wanting to use a Named Range.
If you are hard-coded all the range names in the VBA code, what is the advantage to using those as opposed to just using column references?
I feel like there may be other details about what you are trying to do that I don't know about.

 

[strong100m]

The necessity of using a Named Range comes from the fact that I would like to allocate the data in an specific range, which is determined by the drop-down selections made on the first worksheet.

For example, if the values 2015, Name, Age and Gender are selected from the available drop down lists.

I would concatenate this values to form: 2015NameAgeGender (i.e. 2015Kenny25Male or 2015Mary23Female, or 2016George22Male, etc...)

And then use the VBA code to copy and paste an specific value, under that specific Named Range formed with the information from the drop-down list. In this case, 2015NameAgeGender, or 2016NameAgeGender depending on the selections made.

If there are 60 different possible combinations from the options available under the drop-down lists, it means that there will be 60 available named ranges to save date under them.

Therefore, the goal of the VBA code would be to be able to add data to an specific Named Range, whose name is formed by the selections made. As explained earlier, I could placed each of this 60 Named Ranges each under a different column, but there still will be the need for the code to locate this Named Range and then save the data under the next available row.

Does that makes a bit more sense?

 

[Joe4]

OK. This is getting a bit complex/tricky.
Let's see if we can go back to your original question and get it to work out for named ranges. Just a few questions about that.
- Will there almost be at least one populated cell in your named range (just want to check to make sure you don't start with a situation where you have something like a named range for cells A1:A15, but nothing in any of those cells when the macro is run)?
- Is it possible that all the cells in your named range are already populated? If so, then where do you want to copy the data to?

 

[strong100m]

I know it is complex. But I highly appreciate your help so far.

For the first question, I understand starting from an empty named range can end up making the code even more complex. So for that case I would mind adding a marker like an "-" on the first row just to clear that issue, and make sure there is always at least one occupied row per named range.

For the second question, usually the data points for each named range should not exceed 10 to 15 tops, so I would select a range of about 20 cells (i.e. A1:A20 for the first named range, B1:B20 for the second one, and so on).

 

[Joe4]

OK. Based on all those conditions, give this a shot:

Sub SaveData()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim myCell As Range

    Set ws1 = Sheets("Info")
    Set ws2 = Sheets("Data")

'   Check to see if second cell in named range is blank
    If ws2.Range("Test")(2) = "" Then
'       ...if it is, select that as the cell to paste into
        Set myCell = ws2.Range("Test")(2)
    Else
'       ...otherwise find first blank cell after first cell in named range
        Set myCell = ws2.Range("Test")(1).End(xlDown).Offset(1, 0)
    End If
    
'   Copy data to first blank cell in named range
    ws1.Range("A1").Copy myCell

End Sub

 

[strong100m]

Thank you so much for your help.

I modified the text a bit and took some parts from other threads and I got it to work

The code now takes The text from a specific cell, uses it as the name for "Named Range" and copies the values the first available row under that Named Range.

Here is the code if someone else needs it:

Sub Button1_Click()
    Dim ws1 As Worksheet, ws2 As Worksheet
   
    Dim rngDst As Range
    Dim Target As String

    Set ws1 = Sheets("Info")
    Set ws2 = Sheets("Data")
    Target = ws1.Range("C1").Value

'   Check to see if second cell in named range is blank
    If Range(Target).Cells(1, 1).Value <> "" Then
        Set rngDst = Range(Target).Cells(1, 1).Offset(Range(Target).Rows.Count - 1).End(xlUp).Offset(1)
    Else
        Set rngDst = Range(Target).Cells(1, 1)
    End If
    
'   Copy data to first blank cell in named range
    ws1.Range("A1").Copy rngDst
End Sub