Auto Transferring Data from one cell/row to another sheet [Closed]

Ask a question ssd - Last answered on Jun 18, 2015 at 06:08 AM by vcoolio

Good Morning.

I have a list of transaction in one sheet. With a column showing the sheet name where the data of that row should be transferred.

Please, is there any way to automatically transfer the data of that row to the respective sheet in next available free row, based on the name of the sheet in the column showing the sheet name?

Thanks and best regards,
See more 
plus moins
Hello ssd,

Perhaps the following code will do the task for you:-

Sub TransferAllData()

Application.ScreenUpdating = False

     Dim lRow As Long
     Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A3:A" & lRow)
     MySheet = cell.Value
     cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

Sheets("Master").Range("A3:K" & Rows.Count).ClearContents
MsgBox "Data transfer completed!", vbExclamation

Application.ScreenUpdating = True

End Sub

I have attached my test work book here:-

for you to peruse.

On clicking on the "Transfer Data" button, the code finds the name of each sheet in Column A of the "Master" sheet and transfers the relevant rows of data to each individual sheet. The "Master" sheet is then cleared of all data ready for your next lot of entries.

I don't know how large your data set is so, for the sake of the exercise, in the test work book, I have just used Columns A:K.

I hope that this helps.


Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!