Multi conditional transfer, relocate to existing and new rows VBA macro

Hi

I'm looking for help with creating VBA code for a command button to transfer data from sheet 2 to sheet 1 when certain criteria has been met, but it also needs to update sheet 1 under certain circumstances and move existing data on sheet 1 to a new row and then copy the corresponding data from sheet 2 to the new row in sheet 1 whilst saving copies to worksheets within a new workbook at the appropriate time. I'll try to explain in greater detail below.



On sheet 2
Command button for transfer
1. If any cell in column K on sheet2 has “Please choose Y/N” then Msgbox (“Please update all entries in column K”)
2. The number to transfer (value of U11) must be equal to or greater than the number of available gift cards = in U15, Else Msgbox (“Not enough available”)
3. If U9 > U15 then Msgbox (“Not enough available”)

4. For all rows where column K (sheet 2) = Y
Then find all the rows in sheet 1 where the value in U13 (Sheet 2) equals the value in Column N (sheet 1) AND, Column J (Sheet1) equals “Available” and Column H (sheet 1) equals U14 (sheet 2)
Then for all the rows where column K (Sheet 2) = Y
Copy cells in Column A (Sheet 2) to Column K (Sheet 1)
Copy cells in Column B (Sheet 2) to Column L (Sheet 1)
Copy cells in Column N (Sheet 2) to Column J (Sheet 1)
Copy cells in Column O (Sheet 2) to Column N (Sheet 1)
Copy cells in Column P (Sheet 2) to Column O (Sheet 1)
Copy cells in Column Q (Sheet 2) to Column T (Sheet 1)
Copy cells in Column R (Sheet 2) to Column U (Sheet 1)
Copy cells in Column S (Sheet 2) to Column V (Sheet 1)

Then save an export of the new rows – Column K to X to a new workbook
Sheetname: Transferred
Filename: Transferred + todays Date
Folder Location: C:\ExampleLocation

For all rows where column K (sheet 2) = N
Then save an export of columns A to M on sheet 2 to a folder location under name of Not transferred + todays Date

For all rows where column K (sheet 2) = Reassign
Then find all the rows in sheet 1 where the value in column A (Sheet 2) equals the value in Column K (sheet 1) and Column J (Sheet1) equals “Assigned”
For all the matching Rows In Sheet 1
Change column J to “Closed”
Column S to “Reassigned Closure”
Column T to “Assignment Changed”
Copy cells in Column R (Sheet 2) to Column U (Sheet 1)
Copy cells in Column S (Sheet 2) to Column V (Sheet 1)

Then cut the cells of that row in sheets 1 from A to H to a new line below the last row of Column I sheet1 and from the matching row in Sheet 2
Copy cells in Column A (Sheet 2) to Column K (Sheet 1)
Copy cells in Column B (Sheet 2) to Column L (Sheet 1)
Copy cells in Column N (Sheet 2) to Column J (Sheet 1)
Copy cells in Column O (Sheet 2) to Column N (Sheet 1)
Copy cells in Column P (Sheet 2) to Column O (Sheet 1)
Copy cells in Column Q (Sheet 2) to Column T (Sheet 1)
Copy cells in Column R (Sheet 2) to Column U (Sheet 1)
Copy cells in Column S (Sheet 2) to Column V (Sheet 1)

Then save an export of the new rows – Column K to X to the same workbook under a new sheet
Sheetname: Reassign added
Filename: Transferred + todays Date
Folder Location: C:\ExampleLocation

Then save an export of the old rows – Column K to X to the same workbook under a new sheet
Sheetname: Reassign removed
Filename: Transferred + todays Date
Folder Location: C:\ExampleLocation


Is there a piece of vba code I could use to this within the command button? I'm new to learning VBA so only know little bits of it so far. Any help would be really appreciated.

Thank you

I have some questions. Sheet2 on the attachment has no data, is that how that process starts? And the transfer button is for the code you already started? I am trying to follow along so that I can help. Thanks

Hi

Thanks for looking into this. Yes sheet 2 will start blank and then column A and B will be used to locate duplicates in sheet 1. I've added some dummy data to demonstrate it a bit better and attached the file below. Doesn't have to use the existing code unless you think that's the best way?