• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Copy and Paste Data

Hi Experts,

I would like to request Experts create a VBA code to replace all data at column C with data from column B. Data that need to copy from column B is the last 8 words/Number which is always starts with “FN” or “W”. After copying this data at column C, delete this data from Column B. I have manually copied few data at column C for Experts to get better view. Hope Expert could help.



Cut-Copy-Data.xls
0
Cartillo
Asked:
Cartillo
  • 4
  • 4
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Cartillo,

try this macro:

Sub test()
Dim ws As Worksheet
Dim cel As Range

Set ws = ThisWorkbook.Sheets("Data")
For Each cel In ws.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    cel.Offset(0, 1) = Trim(Right(cel, 8))
Next cel
ws.Columns("B:B").Delete Shift:=xlToLeft
End Sub

Open in new window


Column B will be deleted at the end of the macro. Was that what you wanted? Or do you only want to remove the copied text from column B and leave the rest of the text? That was not quite clear from the description.

cheers, teylyn
0
 
CartilloAuthor Commented:

Hi teylyn,

Thanks for the code. Only delete the 8 data that was copied from Column B, not the whole data.
0
 
CartilloAuthor Commented:
Hi teylyn,

Sorry if my request was nor very clear. After copying the data in column C, only the copied need to be deleted at column B, not the whole data in column B. Currently the whole column being removed. Hope you will consider my request.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
No problem. See if this does what you require.

Sub test()
Dim ws As Worksheet
Dim cel As Range

Set ws = ThisWorkbook.Sheets("Data")
For Each cel In ws.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    cel.Offset(0, 1) = Trim(Right(cel, 8))
    cel = Trim(Left(cel, Len(cel) - 8))
Next cel

End Sub

Open in new window


cheers,
0
 
CartilloAuthor Commented:

Hi teylyn,

I need your advice. Sometime I do have data with this sequence:

T006673A/T006674A

How to handle this type of data? I need to copy 17 instead of just 8.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
OK. Let's see.

Do you need the part of the cell after the last space character?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This version will move the part of text after the last space character to column C and remove the same text from column B

Sub test()
Dim ws As Worksheet
Dim cel As Range

Set ws = ThisWorkbook.Sheets("Data")
For Each cel In ws.Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
    cel.Offset(0, 1) = Trim(Mid(cel, InStrRev(cel, " ") + 1, 99))
    cel = Trim(Left(cel, InStrRev(cel, " ") - 1))
Next cel

End Sub

Open in new window

0
 
Rob HensonIT & Database AssistantCommented:
I know you have specifically asked for VBA but this could be done with formulas:

In cell D2:
=LEFT(B2,LEN(B2)-LEN(E2))

In cell E2:
=IF(LEFT(RIGHT(B2,9),1)="/",RIGHT(B2,17),RIGHT(B2,8))

Copy the formulas down the columns.

However, in your example the items with the two codes separated by /, the codes only have 7 characters each, so 15 in total to be copied not 17 as specified.

The results of the two formula can then be copied and paste values into columns B & C.

Thanks
Rob H
0
 
CartilloAuthor Commented:
Hi teylyn,

Thanks a lot for the code.

Hi Rob,
Thanks for the formula.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now