using Name Column in VBA to paste

[dlz217]

I'm doing a large copy/paste macro from an input form into a data table. I'm still designing both, so I'd like to use a named column in my data table rather than a set column letter.

how can I update the "F" in this so it uses a named column like lastCol instead?

With dataWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With

dataWks.Cells(nextRow, "F").Value = inputWks.Range("lastIn").Value

 

[Michael M]

Try using this, which will find the last column of used data

Dim lc As Integer
lc = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
With dataWks
          nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With

dataWks.Cells(nextRow, lc).Value = inputWks.Range("lastIn").Value

 

[dlz217]

No, I have about 50 fields that are named like this. I want to name the columns too where the value will be pasted so that I can manipulate the data table later on.

 

[Michael M]

Ok, I'm a little confused...do you want the user to specify which column they want to use ??

 

[dlz217]

No. What Id like to do is update the code so instead of using "F" in the code for the destination, it uses a named range for the column such as "StreetCol". That way I can rearrange the columns afterwards and not have to change the VBA.

 

[Michael M]

Do you mean like this??

Dim lastcol As Integer
lastcol = [StreetCol].Column
With dataWks
          nextrow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
      End With
dataWks.Cells(nextrow, lastcol).Value = inputWks.Range("lastIn").Value
End Sub

 

[dlz217]

Yes. Is there a way to make it more simple, in line, without having to Dim name each Integer variable like that? I have 50+ data points. Could you do something like

dataWks.Cells(nextrow, [StreetCol].Column).Value = inputWks.Range("lastIn").Value

where it's just right there? Cause I have a ton of these:

.Cells(nextRow, "C").Value = inputWks.Range("prefixIn").Value
          .Cells(nextRow, "D").Value = inputWks.Range("firstIn").Value
          .Cells(nextRow, "E").Value = inputWks.Range("middleIn").Value
          .Cells(nextRow, "F").Value = inputWks.Range("lastIn").Value
          .Cells(nextRow, "G").Value = inputWks.Range("suffixIn").Value
          .Cells(nextRow, "I").Value = inputWks.Range("liscIn").Value

 

[dlz217]

That does seem to work actually