Move Data from one Column to another

Question

I have this sheet that a vendor sends me with commissions and credit memos. They put the commissions in one set of columns, and the credit memos in another set of columns.. I need to format the sheet so it can be imported into the sales system I use. Can anyone out there help me with some VBA code to go through this sheet and if there is data in the credit memo columns, just move it to the invoice columns?

Below is s a simple sample...but the sheet has 1000's of rows

Any help would be greatly appreciated.

Here is the sample file...Sample File

Answer 1

Try this one.

==============================

Sub MoveColumns()

Dim lastRow As Long 

lastRow = Cells(Rows.Count, "B").End(xlUp).Row 

For i = 5 To lastRow 

    If Cells(i, "B") <> "" Then 

        Cells(i, "G").Value = Cells(i, "B").Value 

        Cells(i, "B").ClearContents 

        Cells(i, "I").Value = Cells(i, "C").Value 

        Cells(i, "C").ClearContents 

        Cells(i, "J").Value = Cells(i, "D").Value 

        Cells(i, "D").ClearContents 

        Cells(i, "K").Value = Cells(i, "E").Value 

        Cells(i, "E").ClearContents 

    End If 

Next i 

End Sub

Answer 2

Or this:

Sub MoveData()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Assuming that the Date header is in A4
    m = Range("A4").End(xlDown).Row
    For r = 5 To m
        If Range("B" & r).Value <> "" Then
            Range("F" & r).Resize(1, 2).Value = Range("A" & r).Resize(1, 2).Value
            Range("H" & r).Resize(1, 4).Value = Range("B" & r).Resize(1, 4).Value
            Range("B" & r).Resize(1, 4).ClearContents
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Answer 3

Copy F4:K4 to M4:R4

M5: =IF($F5="",A5,F5)
N5: =IF($F5="",B5,G5)

O5: =IF($F5="",B5,H5)
P5: =IF($F5="",C5,I5)
Q5: =IF($F5="",D5,J5)
R5: =IF($F5="",E5,K5)

drag down till the end of your data

import M:R into your sales system

Andreas.

Answer 4

Thank you for your reply...this moves the data, but I only want the rows with data in the Credit Memo Columns to be affected. This macro basically removes every above row 17. I only want to move the data in the tows that have data in the credit memo columns....I dont want to remove the data in the columns above that have data in the Invoice columns. If you look at the image before and after, you see I need all of the data above row 17 to remain. When I run this, everything above row 17...is changed to zero. The sample file I gave you was not 100% accurate as far as the columns go...

Here is a new version.

Answer 5

Thank you for your reply...this moves the data, but I only want the rows with data in the Credit Memo Columns to be affected. This macro basically removes every above row 17. I only want to move the data in the tows that have data in the credit memo columns....I dont want to remove the data in the columns above that have data in the Invoice columns. If you look at the image before and after, you see I need all of the data above row 17 to remain. When I run this, everything above row 17...is changed to zero. The sample file I gave you was not 100% accurate as far as the columns go...

Here is a new version.