Find, Copy Partial String, and Paste in New Row Excel VBA

[LWell]

Hello,

I have a workbook with two sheets. One of them is updated monthly with a list of contracts and contract details. However, that sheet is generated automatically by an external program and is quite cluttered and difficult to navigate. My goal is to create a simplified list of every contract in the other sheet.

The issue is that the cluttered sheet displays the names of the contracts in Column B as "CONTRACT NAME: XXXX" whereas I really only want to copy and paste "XXXX". I would greatly appreciate any help in searching Column B for the contract name, copying just the "XXXX" portion, and pasting them subsequently in Column A of the other sheet, starting on Row 12. I hope that makes sense; thank you all very much for any help!

Best,

 

[LWell]

I got it! Thanks so much for your help! Just a notation issue that making it not want to cooperate.

What I used:

Sub Contract_Num()
  Dim lRowCount&

  lRowCount = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
  
  With Sheets("Sheet3").Range("A12").Resize(lRowCount)
    .Formula = "=IF(LEFT([COLOR=#ff0000]'[/COLOR]Sheet2[COLOR=#ff0000]'[/COLOR]!B1,8)=""CONTRACT"",MID([COLOR=#ff0000]'[/COLOR]Sheet2[COLOR=#ff0000]'[/COLOR]!B1,18,12),"""")"
    .Value = .Value
    .Columns("A").Sort key1:=Range("A12"), _
       order1:=xlAscending
  End With
  
End Sub

 

[LWell]

Now, if I wanted to put this in a command button that is located on a different sheet (Sheet1 if you will), would I simply need to activate Sheet3 before the 1RowCount line?

 

[L. Howard]

Now, if I wanted to put this in a command button that is located on a different sheet (Sheet1 if you will), would I simply need to activate Sheet3 before the 1RowCount line?

I am unsure about that.

Hate to just say just give it a try.

A Forms button should work, but I've have trouble with the sort when run from other sheets.

Is there a reason for the command button other than a Forms button or running from a shape on the sheet?

Howard

 

[LWell]

Is there a reason for the command button other than a Forms button or running from a shape on the sheet?

The purpose of the button is ease of use. I will be handing off this project (once I am finished) to someone with very basic Excel skills. I want to make the process of running macros as simple as possible for them. So, no, it is not completely necessary but I believe it would make the process easier for them.

I will continue to look for an answer to this problem. Howard, thank you so much for your help on this.

 

[LWell]

I got it to work how I wanted. For posterity purposes, the command button code is:

Private Sub CommandButton1_Click()
    Worksheets("Sheet3").Activate
    Call Contract_Num
End Sub

And the Contract_Num macro is:

Sub Contract_Num()
  Dim lRowCount&
  lRowCount = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
  With Sheets("Sheet3").Range("A12").Resize(lRowCount)
    .Formula = "=IF(LEFT('Sheet2'!B1,15)=""CONTRACT NUMBER"",MID('Sheet2'!B1,20,4),"""")"
    .Value = .Value
    .Columns("A").Sort key1:=Range("A12"), _
       order1:=xlAscending
  End With
End Sub

 

[L. Howard]

Very good.

Glad it is up and going.

Howard