Solved

Copy and Paste Data

Posted on 2011-09-14
9
233 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:Ingeborg Hawighorst
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
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Expert Comment

by:Ingeborg Hawighorst
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 earned 450 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 32

Assisted Solution

by:Rob Henson
Rob Henson earned 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

27 Experts available now in Live!

Get 1:1 Help Now