Macro edit

[levanoj]

I recorded the following macro:

Sub Macro1()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "trid"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "button_class"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "button_number"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "button_type_"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "incoming_action"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "lac"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "key_sequence"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "personal_label"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "site_ID"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "ring_tone"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "surname"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "given_name"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "organization"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "dn"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "speed_dial_type"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "extended_label_tfe"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "personal_lbl_utf8"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "button_lock_status"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "notes"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "extended_label_bc"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "display_scheme_id"
End Sub

Is there any way it can be shortened?

 

[wigi]

Sub Macro1()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A1:U1").Value = Array("trid", "button_class", "button_number", "button_type_", "incoming_action", "lac", "key_sequence", "personal_label", "site_ID", "ring_tone", "surname", "given_name", "organization", "dn", "speed_dial_type", "extended_label_tfe", "personal_lbl_utf8", "button_lock_status", "notes", "extended_label_bc", "display_scheme_id")
End Sub

 

[VoG]

Try

Sub Macro1()
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Resize(, 21).Value = Array("trid", "button_class", "button_number", "button_type_", "incoming_action", "lac", _
    "key_sequence", "personal_label", "site_ID", "ring_tone", "surname", "given_name", "organization", "dn", _
    "speed_dial_type", "extended_label_tfe", "personal_lbl_utf8", "button_lock_status", "notes", "extended_label_bc", "display_scheme_id")
End Sub

 

[levanoj]

Thanks Wigi, worked nicely.
Just to clarify, I'm trying sort of 'build' on this code layer by layer so now that I have the 1st part started here's what else I'd like it to do:

Once this 1st layer of code is run, I also want the code to then copy the values starting from cell A2:Axxx (the number of values in that row will vary) from the worksheet labeled "Final Stack" and then paste that data onto cell A2 of the new worksheet.

Any thoughts?

 

[VoG]

Try

Sub Macro1()
Dim LR As Long
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Resize(, 21).Value = Array("trid", "button_class", "button_number", "button_type_", "incoming_action", "lac", _
    "key_sequence", "personal_label", "site_ID", "ring_tone", "surname", "given_name", "organization", "dn", _
    "speed_dial_type", "extended_label_tfe", "personal_lbl_utf8", "button_lock_status", "notes", "extended_label_bc", "display_scheme_id")
With Sheets("Final Stack")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:A" & LR).Copy Destination:=Range("A2")
End With
End Sub

 

[wigi]

Sub Macro1()
    Sheets("Final Stack").Range("A2", Sheets("Final Stack").Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Range("A2")
End Sub

 

[levanoj]

Thanks VOG, code working well so far.
Now if possible if the code can then do the following:

insert a value of "2" starting from cell B2 all the way down the column and end adjacent to where the last value exists in column A on the new worksheet

Anyone?

 

[wigi]

Sub Macro1()
Dim LR As Long
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Resize(, 21).Value = Array("trid", "button_class", "button_number", "button_type_", "incoming_action", "lac", _
    "key_sequence", "personal_label", "site_ID", "ring_tone", "surname", "given_name", "organization", "dn", _
    "speed_dial_type", "extended_label_tfe", "personal_lbl_utf8", "button_lock_status", "notes", "extended_label_bc", "display_scheme_id")
With Sheets("Final Stack")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:A" & LR).Copy Destination:=Range("A2")
End With
Range("B2:B" & LR).Value = 2
End Sub

 

[levanoj]

Great Wigi, so far so good.
Almost finished with this code, if it can do the following I think I might be done:

in column D of the new worksheet, if the value of "LINE" is found, it should be replaced by a "1" or if the value is "HUNT+SPEED DIAL" replace with "6"

All help is appreciated.

 

[wigi]

Great Wigi, so far so good.
Almost finished with this code, if it can do the following I think I might be done:

in column D of the new worksheet, if the value of "LINE" is found, it should be replaced by a "1" or if the value is "HUNT+SPEED DIAL" replace with "6"

All help is appreciated.

Do you try things out yourself?

What you last asked, can be done with Replace. I am sure the board has many topics where you can find code on Replace.