Add column for primary key.
I need to add a primary key column labeled PK right before the empty Pos# column. The PK# column should create static numbers, centered horizontally, for each row starting with 1 at the top and ascending toward the bottom. Since the initial order of items is crucial, this primary key will serve as a basis for sorting the sheet moving forward.
Option Explicit
Public Sub CopyTradeHistory()
Dim wks As Worksheet
Dim rngSrc As Range
Dim strSheetname As String
Dim lastrow As Long
Dim Cell As Variant
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wks = ActiveSheet
strSheetname = wks.Name
Set rngSrc = wks.Cells.Find("Account Trade History")
Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
Set wks = ActiveWorkbook.Worksheets.Add
wks.Name = strSheetname & "_output"
rngSrc.Copy wks.Range("A1")
wks.Columns(3).Insert
wks.Columns(5).Insert
wks.Cells(2, 3).Value = "Spread#"
wks.Cells(2, 4).Value = "Spread"
wks.Cells(2, 5).Value = "Leg#"
wks.Rows("1:2").Font.Bold = True
lastrow = wks.Range("a" & 64000).End(xlUp).Row
wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
wks.Range("C3:C" & lastrow).NumberFormat = "General"
wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
For Each Cell In wks.Range("I3:I" & lastrow)
With Cell
.Value = DateSerial(2000 + Day(.Value), Month(.Value), 1)
.NumberFormat = "mmm yyyy"
End With
Next Cell
wks.Columns(3).Insert
wks.Cells(2, 3).Value = "Pos#"
With wks.Range("P3:P" & lastrow)
.Formula = "=text(J3,""mmm yyyy"") & "" "" & K3 & "" "" & L3"
.Value = .Value
.Copy
wks.Range("J3:J" & lastrow).PasteSpecial
wks.Range("J2").Value = "Instrument"
Application.CutCopyMode = False
.ClearContents
'.Formula = "=I3 & E3"
'.Value = .Value
End With
wks.Range("K:L").Delete shift:=xlToLeft
wks.Range("B1").Value = wks.Range("A1").Value
wks.Range("A:A").Delete shift:=xlToLeft
wks.Columns("A:M").EntireColumn.AutoFit
wks.Columns("B:C").HorizontalAlignment = xlCenter
wks.Columns("G:G").HorizontalAlignment = xlCenter
wks.Columns("D:D").HorizontalAlignment = xlJustify
wks.Rows("3:3").Select
ActiveWindow.FreezePanes = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubASKER
Thank you for the addition. Here is the new question. https://www.experts-exchange.com/questions/26885572/Copy-notes.html
ASKER
This answer caused the Instrument column to be removed. I also need an I/S# if possible. If the Instrument and the Symbol are the same on another row, then they should both have the same I/S number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost!
Row 134 and 145 for instance would have the same I/S#. The horizontal and vertical spacing is no longer adjusting to contents and the I/S# should be named that at the top of the column.
Row 134 and 145 for instance would have the same I/S#. The horizontal and vertical spacing is no longer adjusting to contents and the I/S# should be named that at the top of the column.
ASKER
It is the instrument AND the symbol that will make up the I/S#
@rtod2
The "Hire Me" button on a member's profile is there if you want to pay for work to be done on contract, not to draw attention to your questions! ;)
The "Hire Me" button on a member's profile is there if you want to pay for work to be done on contract, not to draw attention to your questions! ;)
ASKER
Actually, you are a top expert. Let's talk about that. I am looking to make a web app out of the final.
ASKER
Actually, you are a top expert. Let's talk about that. I am looking to make a web app out of the final. Note that I am only responding. For mods, I am not offering payment.
Web apps are not my area, I'm afraid, whether you are paying or not.
Not only is Rorya a top expert, he is one of the most amazing Excel and VBA gurus I have ever come across. I learn from his posts all the time
Ta very much! I shall spend the rest of the day feeling bigheaded now. :)
lol - you should. I don't think I've been to a forum where you aren't one of the most prevalent
Open in new window