Report

How to automatically copy selected cells to different sheets?

Ask a question NessaP - Last answered on May 27, 2017 at 12:01 AM by vcoolio
Hello,
I am trying to build an Excel workbook that records information from certain invoices in one sheet and then categorizes them into other specific sheets within the same workbook. How do I copy the selected information from Sheet1 into the individual category sheets automatically?
For example:
Sheet1 (Master Sheet) columns:
Date (01/01/2014) , Vendor (Walmart), Amount (12.00), Category (Groceries)

Copy automatically to:

Sheet2 (Groceries)
Date, Vendor, Amount, Category

Is there a special formula? I'm new to building Excel pages, so I would appreciate all the help or advice I can get. Let me know if I can provide more information.
Thanks!

See more 
Helpful
+1
plus moins
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.
Was this answer helpful?  
NessaP- May 17, 2017 at 02:17 PM
Thank you, vcoolio, for your response. You have seemed to grasp my situation well.

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
Reply
Leave a comment
Helpful
+0
plus moins
Hello NessaP,

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.
NessaP- May 19, 2017 at 06:46 PM
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
Reply
Leave a comment
Helpful
+0
plus moins
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.
NessaP- May 22, 2017 at 05:57 PM
Hello Vcoolio,
Thank you for replying back so quickly. I am refining the sample workbook, and will get back to you as soon as I have it finished and uploaded into Dropbox.

Thanks!
NessaP
Reply
NessaP- May 26, 2017 at 09:45 PM
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
Reply
NessaP- May 26, 2017 at 09:50 PM
Vcoolio,
One more thing, the link I sent you provides you with an exact copy of my worksheet with all included tabs/worksheets. Please read the Master Sheet to view my notes to you and to see how the workbook is set up. Thanks!
NessaP
Reply
Leave a comment
Helpful
+0
plus moins
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.
Leave a comment
Helpful
+0
plus moins
Hello NessaP,

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.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!