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

L

LWell

New Member
Joined
Jun 1, 2015
Messages
20
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,
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Try this in a standard module and run from source sheet or destination sheet.

Assumes the contract number is no longer than 12 characters and nothing to the right of the contract numbers.

If longer than twelve, change the red number 12 in the code to a larger value.

Uses sheet2 and sheet3, change names in code to match your sheets

Howard

Code: 
Option Explicit

Sub Contract_Num()
  Dim lRowCount&

  lRowCount = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
  
  With Sheets("Sheet3").Range("A12").Resize(lRowCount)
    .Formula = "=MID(Sheet2!B1,16,[COLOR="#FF0000"]12[/COLOR])": .Value = .Value
  End With
  
End Sub
 
Upvote 0
Howard,

Thanks for the help. This works well except not every cell in Column B has a contract number. They are placed sporadically throughout the column. Thus I also need a way to find the cell before using =MID to pull out just the number.
 
Upvote 0
Hi LWell,

How about posting about five - ten rows of example data. To see what we are working with.

Howard
 
Upvote 0
Hi Rick,

I get an error on the line you posted, with or without the .Value = .Value

I use the .value to get rid of the formulas on the destination sheet. Not sure how important that is.

Howard
 
Upvote 0
The report I'm given looks something like this:


Date:
Client Name:
CONTRACT NUMBER: 0001
<Information about 0001>
<Information about 0001>
<Information about 0001>
<Information about 0001>
Date:
Client Name:
CONTRACT NUMBER: 0002
<Information about 0002>
<Information about 0002>
Date:
Client Name:
CONTRACT NUMBER: 0003

<tbody>
</tbody>

There is no pattern of when the "CONTRACT NUMBER: XXXX" appears in Column B. So I have to find them all, seek out just the XXXX portions, and paste them in subsequent rows (starting at row 12) on a different sheet. Sorry if that's still unclear!
 
Upvote 0
Give this a try.

Howard


Code: 
Option Explicit

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,8)=""CONTRACT"",MID(Sheet2!B1,18,12),"""")"
    .Value = .Value
    .Columns("A").Sort key1:=Range("A12"), _
       order1:=xlAscending
  End With
  
End Sub
 
Upvote 0
I can tell it's close but I am getting an "update values" dialogue box when I try to run it. (It's a command button sub by the way). Any idea why this would be? Sorry for all the questions; you've been extremely helpful.
 
Upvote 0
On the source sheet, do a Ctrl + f8 > click on Contract_Num > click Run.

What happens?

Howard
 
Upvote 0
A follow up on my last post.

Run the code as in my last post, but not from a command button. Run as:

Code: 
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,8)=""CONTRACT"",MID(Sheet2!B1,18,12),"""")"
    .Value = .Value
    .Columns("A").Sort key1:=Range("A12"), _
       order1:=xlAscending
  End With
  
End Sub

Then, does it work?

Howard
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Automatically move row to sheet based on cell value
Replies
2
Views
438
Warship
Warship
M
  • Solved
Copy and paste multiple matching rows to a new sheet
Replies
6
Views
69
Multim
M
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
163
shknbk2
S
T
  • Solved
VBA code cannot find sheet name
2
Replies
11
Views
172
jolivanes
jolivanes
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
181
ItalianPlatinum
I

Forum statistics

Threads
1,214,823
Messages
6,121,777
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top