[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Copy and Paste Data

Posted on 2011-09-14
9
Medium Priority
?
241 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

Independent Software Vendors: 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 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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

649 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