?
Solved

Copy and Paste Data

Posted on 2011-09-14
9
Medium Priority
?
239 Views
Last Modified: 2012-05-12
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
Comment
Question by:Cartillo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 50
ID: 36540913
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
 

Author Comment

by:Cartillo
ID: 36540996

Hi teylyn,

Thanks for the code. Only delete the 8 data that was copied from Column B, not the whole data.
0
 

Author Comment

by:Cartillo
ID: 36541080
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
Technology Partners: 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!

 
LVL 50
ID: 36541562
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
 

Author Comment

by:Cartillo
ID: 36541620

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
 
LVL 50
ID: 36541651
OK. Let's see.

Do you need the part of the cell after the last space character?
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1800 total points
ID: 36541717
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
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 200 total points
ID: 36541770
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
 

Author Closing Comment

by:Cartillo
ID: 36542098
Hi teylyn,

Thanks a lot for the code.

Hi Rob,
Thanks for the formula.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question