Copy cells relative to active cell

Thanks for that,

I'll try it as soon as I get to the laptop I'm doing it on. In the meantime, I meant to type 700Kb (not 70Kb) but I'll put it up anyway. (better to apologise than ask permission :-))

2 consecutive posts, isn't that considered bad form on some forums? Sorry about this. I was focussed on uploading the spreadsheet and didn't address the rest of your post.

Anyway, as you may see if you look at the workbook, there will be more than one worksheet for which this needs to work. My friend has several agencies for which he does work as well as private clients. He wants a different sheet for each of these clients. So one client is 2nd Avenue Realty another is The Professionals Real Estate. Thus, I have named the sheets; 2nd Avenue, Professionals and will name others as they are added.

Hope this helps. :-)

Cheers,

Grouty

Hi JapanDave

In normal operation the rows will be completed consecutively. So. no blank rows. The names and addresses are not matched one for one. So, I set it up so that there are 2 validated lists (columns A and B) that contain the addresses and the names of the contact person. These would be completed by selecting from a drop down menu. My friend has many fine qualities but lacks basic keyboard skills and takes an age to type anything.

For this reason I was even trying to get the date to enter automatically when there was something entered in column D. However, every way I tried it the date would change when the spreadsheet was opened again the following day.

Many thanks for looking at this.

Cheers,

Grouty

Deleted due to double post - browser crash! Sorry.

Steve, my friend with the small business, does gardening jobs and other handyman stuff. many of the lawns he cuts get done every 2-3 weeks and so all the details for the job are the same with the exception of the date. Thus, copying the necessary details from a line above would make sense and reduce the evil typing.

As you can see from the workbook, the yellow columns are unprotected while the pink ones are protected. In truth, the cells to be copied and pasted from and to each row would ideally just be in columns: D, E, G, H, I, J, K and O. The more I look at this the more awkward it becomes. :-)

Do you think this is do-able or have I set this up badly?

As for the date, it would need to be changed from one instance of a job, on one row, to the next instance of that same job 2 or 3 weeks later on the next blank row. Even when a job is being entered manually it would be great if it would just appear automagically whenever an entry was being input in a row. The problem is that if I enter it as something simple like:

Please Login or Register  to view this content.

then when the workbook is opened on another date, all the dates change to the present date. Thus Steve has no record of what job was done when as all the dates are the present date.

I tried a few bits of code I found on various web sites but they don't seem to have worked. My fault I'm sure but...

As it stands I think I can train Steve to use the "ctrl-:" shortcut for date entering. It's not as elegant and it involves using the keyboard but anything else is beyond me at the moment.

As I am writing down my wishes and desires I might as well add this: I'd like the cell containing the invoice number in the pasted row (in column C) to become selected after the pasting. This is because the button marked "Print Selected Invoice" pastes the entry in this cell for a VLOOKUP to be used in producing the invoice as a PDF. This would require Steve to select the right invoice in column C which he could probably do but no harm in asking?

Thanks for all your time and effort on this so far. It is greatly appreciated.

Cheers,

Grant

Originally Posted by JapanDave

The date issue should not be a problem, so Steve does not have to enter the date. And the last request is not a problem either. But looking at the data, the data in row 3 and row 5 are completely different?
Or are asking that the data from the previous job for that client be pasted in the next available cell? And that would mean that there would not be any particular pattern as to when the next job would be?

I'm sorry, I didn't put enough thought into the data I was typing in to test stuff out. What is in the workbook I sent you is not very realistic. So, assuming that the job is a simple repeat with no extra items, I was thinking it might work like this:

1. Steve mows the lawns at 27 Everest St again which is administered by 2nd Avenue Realty and for whom the contact person is Grant so he looks in the workbook and selects the 2nd Avenue sheet.
2. He then looks back to find the last time he mowed at that house and selects the cell containing the invoice number for that occasion (cell C3 in this case).
3. Next Steve hits the Copy Job Details button and the macro copies the relevant details and pastes them into the next empty row.
4. The cell in column C of the row that was just pasted in is selected (ready for Steve to press the Print Selected Invoice button)

There will be times when an exact copy would not be what was required. So Steve would mow the lawns at 27 Everest St but would also spray for pests or some other additional work requiring materials or equipment hire as well as additional hours. In this case he could copy as described above then add the extras - which is why the copy/paste macro shouldn't just print automatically.

I hope this is clear. It all makes sense inside my head.

Cheers,

Grouty

Oh this is so close to what I need.

Just a little misunderstanding. Each tab/worksheet is a company, the list of addresses on each sheet are properties the companies are hiring Steve to upkeep. Thus, the "Companies" column is redundant.

Using it as it is though, I can see that it does so much of what I was trying to achieve. It does the copy and paste thing much better than I had hoped. I had thought it would have to be a 2 action thing rather than just a selection from a menu. Great!

I notice a couple of cosmetic things that might be to do with different versions of Excel. The buttons I had for Show/Hide the "Customer Addresses" seem to have faded away - I can still see them and they still work but with no colour. Should be easy for me to fix.

I tried using the "Print Selected Invoice" button and, as you predicted, it didn't work because of the extra columns. However, it also gave me a message that the workbook I was trying to paste to is using a different date system. I've seen this before but am confused as I am not trying to paste into a different workbook. I've obviously done something wrong as, this time, a different sheet actually opened. Not sure if you can help me with this too?

As always, thanks for all the help.

Cheers,

Grouty

I'm pretty sure I have LOTS of unnecessary stuff in my code. Lots of trial and error. As for the one you spotted selecting cell C3, I had some code that copied whatever was selected and pasted it elsewhere. I stuffed things up royally a number of times by not having the correct cell selected when I ran the macro. I decided it was safer to know where the selection was. I discovered that Excel doesn't "undo" actions carried out by VBA code/macros.

Thanks again.

Cheers,

Grant

Hi JapanDave

You're going to think I'm a real dunce but I'm having trouble with the 1st part of this. If I right click the 2nd Avenue tab I see what is in this image:

ScreenSnapzRtClick.gif

I don't see any "window to the right". I'm sorry. Might this be a Windows vs Mac thing?

Absolutely brilliant. It works perfectly!

I'm sorry for being a bit slow. I didn't realise you just needed me to insert the 1st bit of code into the worksheet in the VBA editor. I thought you meant the Workbook/worksheet. Duh! Couldn't see how that was possible but it didn't stop me trying.

I did it all as you described and it does exactly what I need. I'm looking forward to getting the rest of it done now and giving it to Steve. Might even make it his Christmas present.

Thanks again for all the effort you put into this. I think I'll go get a VBA book and try and work out what all the code means.

Have a great Christmas and a happy New Year.

Cheers,

Grouty

PS if you know of any good VBA primers let me know.

Hi again JapanDave,

I have changed the title to include [SOLVED] and I did the little star thing yesterday. I tried again today but it won't let me do it again until I do so for someone else. Shame as I think you deserve it.