Payment Transfer

[Closed]
Report
-
Posts
1311
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 28, 2021
-
Hello,
I have a spreadsheet with the following columns on a sheet called PAYMENTS
D=name E=date F=Payment G=ref#

I also have additional sheets, one for each name in column D on the PAYMENTS sheet. I need a code that will look at F and if there is a payment, copy the entire row to the sheet who's name matches the name in D on the PAYMENTS sheet. This should copy to the next blank row and not overwrite anything.

Thanks and look forward to your help.


2 replies

Posts
1311
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 28, 2021
233
Hello Michael,

Are Columns A To C blank?

Cheerio,
vcoolio.
Posts
1311
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 28, 2021
233
Hello again Michael,

Regardless, the following code may do the task for you:-


Sub TransferData()

Application.ScreenUpdating = False

         Dim lRow As Long
         Dim lCol As Long
         Dim i As Integer
         Dim MySheet As String
lRow = Range("D" & Rows.Count).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Sheet1.Select

For i = lRow To 2 Step -1
          MySheet = Cells(i, 4).Value
          If Cells(i, 6) <> "" Then
          Range(Cells(i, 1), Cells(i, lCol)).Copy Sheets(MySheet).Range("A" & Rows.Count).End(3)(2)
          Range(Cells(i, 1), Cells(i, lCol)).Delete
          Sheets(MySheet).Columns.AutoFit
    End If
Next

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Following is the link to my test work book:-

https://www.dropbox.com/s/8ce29y9xesuz8mz/MichaelT%28Master%20sheet%20to%20multi%20sheets%29.xlsm?dl=0

The code transfers data to each individually named sheet (based on Column D names) as long as there is a value in Column F. The code then deletes the "used" data from the Payments sheet.

Click on the blue button to see the code at work.

I hope that this helps.

Cheerio,
vcoolio.

P.S. : I noticed that you are operating a Mac. I do not have any experience with Macs but I believe that Excel for Macs should still work fluently when VBA is added to a spread sheet. One way to find out!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!