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]

Welcome to the Board!

Looks like you have some confusion between ws2 and ws3. You declared ws1 and ws2, but then use ws1, ws2, and ws3 in your code.

 

[strong100m]

I apologize, that was a typo:

Here is the correct code I have gotten so far:


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

End Sub

 

[strong100m]

I apologize, that was a typo:

Here is the correct code I have gotten so far:


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

End Sub

 

[Joe4]

Is "Test" a named range?
I don't think Range("Test" & LastRow) is a valid range reference.
Try:

[COLOR=#333333]ws1.Range("A1").Copy [/COLOR][COLOR=#FF0000]ws2[/COLOR][COLOR=#333333].Range("A" & LastRow)[/COLOR]

 

[strong100m]

Yes, "Test" is a named range.

I cannot reference an specific column since I will have on the final file several named ranges on the same worksheet.

What I am planning on doing is gathering text from several drop-down lists into one cell, for example: "2015NameAgeGender". This should be the name of one of the name ranges that I will create on Worksheet 2.

Then have the VBA lookup exactly the first available row under that named range, copy a value from another worksheet, and paste it into that last available row previously looked up.

I hope that makes sense and provides guidance to help with this code.

 

[Joe4]

You said you wanted to paste it "to the first available row in a specific named range ("test") in worksheet "Data". "
But which column do you want to paste it to?

 

[strong100m]

I am planning on using single column named ranges

i.e: Range "2015NameAgeGender" will reference to Range A1:A15
Range "2016NameAgeGender" will reference to Range B1:B15
Range "2017NameAgeGender" will reference to Range C1:C15
and so on...

Thank you for your help with this code.

 

[Joe4]

OK. Will you ever have different named ranges for the same column?

For example, you said: Range "2015NameAgeGender" will reference to Range A1:A15
Is it possible to have another named range in column A (say for A16:A30)?
Or will there never be more than one named range in any one single column?
And is it possible to have any data in that column under the named range?

 

[strong100m]

I was considering the option of having only one named range per column for simplification purposes.

I considered having multiple named ranges per column would make things a bit more complicated, but I am open to arrange the named ranges one under the other in the same column if you consider from your expertise that this may make the code easier.

For your last question, yes, the idea is to select at least 100 cells per named range (i.e. A1:A100) since I would have to keep adding information to the named range over time.