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

Report
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
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

1 reply

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!