Previous Topic Next Topic

Auto Transferring Data from one cell/row to another sheet

Closed
ssd - Jun 18, 2015 at 04:57 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - 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

1 response

Answer 1 / 1
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jun 18, 2015 at 06:08 AM
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.