Auto Transferring Data from one cell/row to another sheetClosed

Question

Hello, 

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,
Subash

vcoolio · Answer

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:-

https://www.dropbox.com/s/enwxmszy023htf4/ssd.xlsm?dl=0

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.

Cheerio,
vcoolio.

Auto Transferring Data from one cell/row to another sheet

1 response

Similar discussions

Subscribe To Our Newsletter!