How to automatically copy selected cells to different sheets?
Closed
NessaP
-
May 8, 2017 at 02:58 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 27, 2017 at 12:01 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - May 27, 2017 at 12:01 AM
Related:
- How to automatically copy selected cells to different sheets?
- Transfer data from one excel worksheet to another automatically - Guide
- How to take screenshot of selected area in excel - Guide
- How to automatically save photos from messenger to gallery - Guide
- Pc restarts automatically - Guide
- Download automatically while roaming - Guide
5 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 8, 2017 at 10:42 PM
May 8, 2017 at 10:42 PM
Hello NessaP,
I assume that you have a number of categories with each having an individual sheet. You would like each relevant row of category data to be transferred from the Master sheet to its individual sheet once a category is typed into a cell in Column D.
Is the "used" data to be cleared from the Master sheet once transferred to its individual sheet?
How many categories are there?
Let us know if the assumptions are correct.
Cheerio,
vcoolio.
I assume that you have a number of categories with each having an individual sheet. You would like each relevant row of category data to be transferred from the Master sheet to its individual sheet once a category is typed into a cell in Column D.
Is the "used" data to be cleared from the Master sheet once transferred to its individual sheet?
How many categories are there?
Let us know if the assumptions are correct.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 18, 2017 at 05:01 AM
May 18, 2017 at 05:01 AM
Hello NessaP,
I'm assuming that you have all the individual sheets already set up so the following code should work for you:-
The code is a Worksheet_Change event hence, every time that you place a value in a cell in Column I and then click away (or press enter or down arrow), the relevant row of data will be transferred from the Master sheet to the relevant individual sheet.
This type of code needs to be placed in the worksheet module. To implement the code:-
- right click on the Master sheet tab.
- Select "view code" from the menu that appears.
- In the big white field that then appears, paste the above code.
Let us know if this works for you and remember to test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
I'm assuming that you have all the individual sheets already set up so the following code should work for you:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Intersect(Target, Columns("I:I")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
The code is a Worksheet_Change event hence, every time that you place a value in a cell in Column I and then click away (or press enter or down arrow), the relevant row of data will be transferred from the Master sheet to the relevant individual sheet.
This type of code needs to be placed in the worksheet module. To implement the code:-
- right click on the Master sheet tab.
- Select "view code" from the menu that appears.
- In the big white field that then appears, paste the above code.
Let us know if this works for you and remember to test the code in a copy of your work book first.
I hope that this helps.
Cheerio,
vcoolio.
Hello Vcoolio,
That formula did not work. I followed your directions, but the test entry I made in the Master sheet did not appear in the category worksheet. Your explanation made sense and I think we are on the right track. I have prepared a test Excel workbook, set up similar to the one I'm working off of. I wonder if there is someway I could send that to you so you could see exactly what I'm trying to do? Or can I explain it in a better way? How could I assist you with the best information possible? Thanks so much for your help!
NessaP
That formula did not work. I followed your directions, but the test entry I made in the Master sheet did not appear in the category worksheet. Your explanation made sense and I think we are on the right track. I have prepared a test Excel workbook, set up similar to the one I'm working off of. I wonder if there is someway I could send that to you so you could see exactly what I'm trying to do? Or can I explain it in a better way? How could I assist you with the best information possible? Thanks so much for your help!
NessaP
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 19, 2017 at 06:57 PM
May 19, 2017 at 06:57 PM
Hello NessaP,
Yes, supplying a sample of your work book is the best method. That way we can test/experiment with the code and your work book's set up.
Upload your file to a free file sharing site such as Drop Box, ge.tt or Sendspace and then post the link to your file back here.
We should then be able to sort it out for you.
Cheerio,
vcoolio.
Yes, supplying a sample of your work book is the best method. That way we can test/experiment with the code and your work book's set up.
Upload your file to a free file sharing site such as Drop Box, ge.tt or Sendspace and then post the link to your file back here.
We should then be able to sort it out for you.
Cheerio,
vcoolio.
Hello Vcoolio,
Below you will find the link to my document. Further explanations are contained within. Let me know if I can help you with any more information! Thanks for all your help!
https://www.dropbox.com/s/7tmt7lqd6sd636e/Running%20Purchase%20Ledger%202017%20-%20Copy.xlsx?dl=0
Sincerely,
NessaP
Below you will find the link to my document. Further explanations are contained within. Let me know if I can help you with any more information! Thanks for all your help!
https://www.dropbox.com/s/7tmt7lqd6sd636e/Running%20Purchase%20Ledger%202017%20-%20Copy.xlsx?dl=0
Sincerely,
NessaP
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 22, 2017 at 07:38 PM
May 22, 2017 at 07:38 PM
Hello NessaP,
Thanks for that.
In the meantime, just check that the criteria that you are typing into any cell in Column I is spelled exactly the same as the tab names (or vice versa). It may be as simple as that, you never know!
Cheerio,
vcoolio.
Thanks for that.
In the meantime, just check that the criteria that you are typing into any cell in Column I is spelled exactly the same as the tab names (or vice versa). It may be as simple as that, you never know!
Cheerio,
vcoolio.
Didn't find the answer you are looking for?
Ask a question
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
May 27, 2017 at 12:01 AM
May 27, 2017 at 12:01 AM
Hello NessaP,
Okie dokie. Here we go:-
You'll notice a slight modification to the code. Place it in the Master Ledger module as per the instructions in post#3.
Columns I, J and K are now active so, once you enter the relevant criteria in a cell in one of these columns, and click away (or press enter or down arrow), the relevant row of data will be transferred to the relevant individual sheet.
I've added an extra line of code which will take you directly to the individual sheet to which data has just been transferred. If this becomes a little annoying for you, just remove line 10 from the code above.
Remember to make sure that the category that you type into a cell in Columns I, J or K is spelled exactly the same as the sheet tab name. To minimise errors in this regard, it may be worth your while to create validation lists for all three columns for all the categories. This way, any user can just select a category from the drop down list that appears rather than type it in.
I hope that this helps.
Cheerio,
vcoolio.
Okie dokie. Here we go:-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Intersect(Target, Columns("I:K")) Is Nothing Then Exit Sub
Target.EntireRow.Copy
Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheets(Target.Value).Columns.AutoFit
Sheets(Target.Value).Select
Application.CutCopyMode = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
You'll notice a slight modification to the code. Place it in the Master Ledger module as per the instructions in post#3.
Columns I, J and K are now active so, once you enter the relevant criteria in a cell in one of these columns, and click away (or press enter or down arrow), the relevant row of data will be transferred to the relevant individual sheet.
I've added an extra line of code which will take you directly to the individual sheet to which data has just been transferred. If this becomes a little annoying for you, just remove line 10 from the code above.
Remember to make sure that the category that you type into a cell in Columns I, J or K is spelled exactly the same as the sheet tab name. To minimise errors in this regard, it may be worth your while to create validation lists for all three columns for all the categories. This way, any user can just select a category from the drop down list that appears rather than type it in.
I hope that this helps.
Cheerio,
vcoolio.
May 17, 2017 at 02:17 PM
No, I do not want the "used" information removed from the Master sheet when it is copied to the individual sheets.
I have 12 categories lined up horizontally (each with their own column) in the following order:
Date, PO#, Description, Amount, Vendor, Account, Dept. Cat., Invoice/Acct. #, Budget Cat., User, Date Ent., Notes.
The category Column that designates each individual sheet is the Budget Cat. and it is in Column I.
Thank you so much for helping me on this. Let me know if I can provide any further information!
NessaP