Copying cells with data to new worksheet

  • Good morning,

    I have a job I've been asked to do and I don't know how to go about it.

    I have a Worksheet for each person, with data for each matter they work on. In one of the columns, at present there is no data, however, when data is entered over the course of a week, I want to be able to pull that data (from the one column) into a new worksheet. Firstly below is the straight up no frills worksheet.

    Information will get added to the last column over time. I want to be able to pull out rows that have data in the final column into a new sheet ... a button would be amazing.

    I'm sorry i'm so basic and I am really hoping you can help me.

  • Hi thanks for the reply :)

    Yes, that's right - there are four columns A-D. If D has data in it, I want to extract those particular rows and throw them in another worksheet called maybe 'results'.

  • Attac an example workbook, but moving the data is really unnecessary. You can just use AutoFilter to view the data whenever required.

    Hi Roy, that sounds good and I will look into how to go about it. :)

  • Hello Railea,

    Are you happy with Roy's suggestion or do you have another reason/need for moving relevant data to another worksheet (and, I'm assuming, deleting it from the main sheet)?



  • Hey vcoolio - I would prefer to be able to move the data - it's for two solicitors - so not computer savvy really - so it would be a way of reporting to them what they needed to look at ... i hope that makes sense - i don't think they're going to want to know how to autofilter :)

  • Hello Railea,

    I thought so!

    I'm assuming that you'd want to transfer relative data (associated with the two individual Solicitors) into their own worksheet.

    Hence, could you upload a sample of your workbook showing exactly how you'd like this to work. Make sure that the sample is an exact replica of your actual workbook. Having a sample to work/test with will make it easier to resolve this for you. If the data is sensitive then please use dummy data.



  • Hi vcoolio that's right A:D and D is the last column and below is an example - not very good screen shots sorry - you can see the third entry down has information in it - 01.01.2021 - Report of Dr ... out of all the columns it's only those with information in Column D that i'm looking to copy into another worksheet. Thank you :)

  • Hello Railea

    Thanks for the sample. I've a couple of more questions for you:-

    1) It appears that you need Matt's and Joan's matters kept separate and placed into their individual "Results" sheets once a matter is dealt with. Is this correct?

    2) Based on the set out of the individual source sheets and destination sheets, once an entry is made in Column E of a source sheet, you would like the entry to be matched to the same matter number in the destination sheet and placed in Column D in the same row as the matching matter number. In other words, it's only the Column E entry that you're interested in transferring over to the destination sheets. (Column E is the CTBDW column in the source sheets and Column D is the CTBDW column in the destination sheets). Is all this correct?

    My assumption for question #2 is based on the sample showing that your data uses Columns B:E. Column A is not used in any of the worksheets as per your previous advice.



  • Hi :)

    1. Yes, Matt and Joan will be working on different matters, so separate sheets, and the results placed into those individual sheets.

    2. Yes - what I am thinking is if something goes into Column E, that whole row would get put into the results sheets, if that's possible. So essentially, the results page starts out empty, but gets populated with rows that now have information in Column E.

    3. Yes, sorry there's nothing in column A - so it's B to E.

    Thank you so much!

  • Your data needs to be organised correctly. There should be no completely empty rows or cplumns within the data table.

    This will mean that the data can be used properly in Excel and if you really need to use VBA then the code can be made to tun most efficiently.

  • Hello Railea,

    I've assumed that you'd want a button on each of the two source sheets simply because each source sheet will be worked on separately/individually and when a User has the need to transfer data as a matter arises. Hence, try this:-

    As you can see, there are two codes above (identical other than the sheet references) which will operate separately on the individual source sheets.

    The codes clear the destination sheets with each transfer of data as I've also assumed that you would need to keep all data in the source sheets. This will basically refresh the destination sheets and prevent duplication of data.

    I've attached your sample workbook with the codes implemented and assigned to the buttons on the two source sheets. Have a play with the file and see what you think.

    Take note of what Roy has said about properly using all rows; that's what they're for! ;)

    I hope that this helps.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!