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

ssd - Jun 18, 2015 at 04:57 AM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen
- Jun 18, 2015 at 06:08 AM
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
See more 

1 reply

vcoolio 1173 Posts Thursday July 24, 2014Registration dateModeratorStatus July 15, 2018 Last seen - Jun 18, 2015 at 06:08 AM
0
Thank you
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.