Transfer data fron one column to another

Panoos64 · Jul 9, 2018

Hi all, I would like to write a VBA code with below command

- If Col. “B” contains the letters INV and INV1 should move the data from col. “D” to "B" and data from col. “E” to “D”.

Below in sch.1. are the data and in sch.2. is the expected result
Thanks in advance


Sch.1.

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl67, width: 60"]B[/TD]
[TD="class: xl67, width: 64"]C[/TD]
[TD="class: xl67, width: 64"]D[/TD]
[TD="class: xl67, width: 64"]E[/TD]

[TD="class: xl64"]1[/TD]
[TD="class: xl68"]31-10-17[/TD]
[TD="class: xl69"]SUPP INV[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]35037[/TD]
[TD="class: xl65"]258.00[/TD]

[TD="class: xl64"]2[/TD]
[TD="class: xl68"]05-11-17[/TD]
[TD="class: xl69"]SUPP INV1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]35128[/TD]
[TD="class: xl65"]456.00[/TD]

</tbody>

Sch.2.

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl68"]A[/TD]
[TD="class: xl69, width: 60"]B[/TD]
[TD="class: xl69, width: 64"]C[/TD]
[TD="class: xl69, width: 64"]D[/TD]
[TD="class: xl69, width: 64"]E[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl70"]31-10-17[/TD]
[TD="class: xl71"]35037[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]258.00[/TD]
[TD="class: xl67"][/TD]

[TD="class: xl66"]2[/TD]
[TD="class: xl70"]05-11-17[/TD]
[TD="class: xl71"]35128[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67"]456.00[/TD]
[TD="class: xl67"][/TD]

</tbody>

My Aswer Is This · Jul 9, 2018

So if column B says Soup what should happen?

Or will there always be only

SUPP INVI

Or

SUPP INV

My Aswer Is This · Jul 9, 2018

Assuming column B will only have the two values you mentioned then try this:

Code:

Sub Test()
'Modified 7/9/2018 4:35 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:E" & Lastrow).Copy Sheets(2).Range("A1")
Sheets(2).Range("B1:C1").EntireColumn.Delete
Sheets(2).Columns("C").Insert
Application.ScreenUpdating = True
End Sub

Panoos64 · Jul 9, 2018

Hi My Asw, if in "B" contains at left of INV & INV1 any word, text or date the code should run. The key is that, the cells should contains the above letters (INV or INV1).
Thank you so much for your interesting to resolve my project.
HV a great day

Panoos64 · Jul 9, 2018

My Asw, the above code it runs but it transfer all columns data from "E" to "D". Just the code should transfer the columns data if "B" column, contains in rows the letters INV & INV1. Thanks once again for your support.

My Aswer Is This · Jul 9, 2018

Try this:

Code:

Sub Test()
'Modified 7/9/2018 6:30 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
For i = 1 To Lastrow
    If InStr(Cells(i, "B").Value, "INV") Or InStr(Cells(i, "B").Value, "INVI") Then
        Cells(i, 1).Copy Sheets(2).Cells(Lastrowa, 1)
        Cells(i, "D").Copy Sheets(2).Cells(Lastrowa, 2)
        Cells(i, "E").Copy Sheets(2).Cells(Lastrowa, 4)
        Lastrowa = Lastrowa + 1
    End If
Next
Application.ScreenUpdating = True
End Sub

Panoos64 · Jul 10, 2018

My Asw, no it doesn't work. It delete rows which i need and also it transfer the data in Sheet2. Also it mixed the data. The code should adjust the data in the same Sheet which is named "Summary". In Simply words the code should run as follow:

Run trough col. "B" and where find the text INV or INV1 should place in col. "B" the data from col. "D" and the data from col. "E" should place them in col. "D".

However i appreciated the work you done for me and your time spent for my project.
Hv a great day

My Aswer Is This · Jul 10, 2018

Your original image showed Sh1 and Sh2
Which normally mean to me Sheet(1) and Sheet(2)

So now try this:

Code:

Sub Test()
'Modified  7/10/2018  9:08:39 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets("Summary").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If InStr(Cells(i, "B").Value, "INV") Or InStr(Cells(i, "B").Value, "INVI") Then
        Cells(i, "D").Cut Cells(i, "B")
        Cells(i, "E").Cut Cells(i, "D")
    End If
Next
Application.ScreenUpdating = True
End Sub

Panoos64 · Jul 10, 2018

Thank you so much My Asw, now it works perfect and according to my data. I express my apologies that i bother you so many times and in addition i am sorry for my original message. Just i wanted to show in Sch.1. how was the original data when i download it through accounting system and in Sch.2., how is the expected result.
Eventually all well and i would like to thank you once again. All the best to you.
Have a great day

My Aswer Is This · Jul 10, 2018

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Transfer data fron one column to another

Panoos64

Well-known Member

Excel Facts

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Panoos64

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Transfer data fron one column to another

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock