Solved

Copy and Paste Data

Posted on 2011-09-14
9
232 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
  • 4
  • 4
9 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility

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
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Cartillo
Comment Utility

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

Expert Comment

by:teylyn
Comment Utility
OK. Let's see.

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

Accepted Solution

by:
teylyn earned 450 total points
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
Comment Utility
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
Comment Utility
Hi teylyn,

Thanks a lot for the code.

Hi Rob,
Thanks for the formula.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now