Changing range names using VBA

Hi,

I created this vba code (from scratch, I'm very proud of it). The code is assigned to a text box and when it's clicked the macro starts running. It's a pretty basic copy/paste so far but I want to add a few lines that does the following after the marked line:

I want the macro to do a cells(1,lastrow+1).select and change the range name to whatever is in cells(1,lastrow+1) and substitute any spaces " " with "_". Then I want it to select the entire columns(lastrow+1) and change the name to whatever is in cells(1,lastrow+1) and add a "Col" at the end of the name.

This is probably very easy to code, but I can't figure it out.

Sub LäggTillNyProdukt()
    Dim LastColumn As Integer
    Dim LastRow As Integer
    
    'Lägg till ny uppdateringsknapp
    Sheets("uppdatera").Select
    'Hittar sista raden och ger den ett nummer
    LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    Rows(LastRow).Copy
    Rows(LastRow + 2).Select
    ActiveSheet.Paste
    Range("B3").Copy
    Cells(LastRow + 2, 1).Select
    ActiveSheet.Paste
         
    'Lägg till information för lista
    Range("B3").Copy
    Sheets("Lista").Select
    'Hittar sista raden och ger den ett nummer
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Cells(LastRow + 1, 1).Select
    ActiveSheet.Paste
    Cells(LastRow + 1, 1).Copy
    Cells(1, LastRow + 1).Select
    ActiveSheet.Paste
    
'ADD NEW LINES HERE

    'Gå tillbaka till Uppdatering
    Sheets("Uppdatera").Select
    Range("B3").Select
    Application.CutCopyMode = False
       
End Sub

I believe this does what you are asking for.

Sub LäggTillNyProdukt()
    Dim LastColumn As Integer
    Dim LastRow As Integer
    
    'Lägg till ny uppdateringsknapp
    With Sheets("uppdatera")
        'Hittar sista raden och ger den ett nummer
        LastRow = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        .Rows(LastRow).Copy .Rows(LastRow + 2)
    
        .Range("B3").Copy .Cells(LastRow + 2, 1)
    End With
         
    'Lägg till information för lista
    With Sheets("Lista")
        'Hittar sista raden och ger den ett nummer
        LastRow = .Cells("A65536").End(xlUp).Row
        Sheets("uppdatera").Range("B3").Copy .Cells(LastRow + 1, 1)
        .Cells(LastRow + 1, 1).Copy .Cells(1, LastRow + 1)
    
'ADD NEW LINES HERE
        ActiveWorkbook.Names.Add Name:=Replace(.Cells(1, LastRow + 1), " ", "_"), RefersToR1C1:="=" & .Name & "!" & .Cells(1, LastRow + 1).Address
        ActiveWorkbook.Names.Add Name:=Replace(.Cells(1, LastRow + 1), " ", "_") & "Col", RefersToR1C1:="=" & .Name & "!" & .Cells(1, LastRow + 1).Address
    End With


    'Gå tillbaka till Uppdatering
    Application.CutCopyMode = False
       
End Sub

Note that you could get rid of copying altogether by simply saying

.Cells(1,LastRow +1).Value = .Cells(LastRow + 1,1).Value

but this would only transfer the values, not the formatting or formulas. If you wanted the formulas instead, you would replace.Value with .Formula. If you want the formatting, then you would just leave the .Copy method above.

Great, thanks for the help. I just have a one modification I want to make.

    ActiveWorkbook.Names.Add Name:=Replace(r, " ", "_") & "Col", RefersToR1C1:=r.EntireColumn.Select

where r is the last cell in a row. The code works well but the name range refers to =TRUE when I check the reference under instert -> names -> define. It should refer to something like Lista!$G:$G (or whatever column r was in.

Or

Sub LäggTillNyProdukt()
  with Sheets("uppdatera")
     x=.cells(rows.count,1).end(xlup).row
    .rows(x).copy .rows(x)
    .cells(x+2,1) = .range(B3").value
    .cells(x+2,1).name = replace(.cells(x+2,1)," ","_")
    .cells(1,1).resize(x+2).name = replace(.cells(x+2,1)," ","_")& "_Col"
    Sheets("Lista").Cells(Rows.Count, 1).End(xlUp).offset(1,1) = .Range("B3").value
  end with
End Sub