Need an IF/THEN cut and paste marco please...

I have attached a file. I would like to have a marco available that upon the keystroke of 'control+Z', any row on tab 'freezers' that has a date in column A, will be copied from column A through Column G onto the next available row on the next tab called Slots..but I don't want any duplicates (there are duplicate names but other information is different).

Is this possible? Do you need more info? TEST INVENTORY.xlsThanks for any help!!!

I'm not too sure what you are calling a duplicate, so I took a stab and this is what I got:

Please Login or Register  to view this content.

You should be able to modify that when we figure out what duplicate means in this application.

You should still be able to use "ctrl+Z" as the windows shortcut for undo is "ctrl+z". You can actually use "ctrl+z", but you will lose that as a shortcut for undo.

Thank you so much for this! When I say duplications, I mean that the data will stay on the first tab and when I go back the next day and enter more information and then add the date and run the macro, I dont want the data from yesterday to be copied and pasted into the second tab again since it should already be there.

Also, I apologize for my ignorance in this as I really have no formal knowledge of Excel. my boss asked if I could get this done so I am doing my best. If I copy and paste your code into a new module in Visual Basic, will I then be able to key control+Z and have it work or is there something else I will need to do?

Thank you so much!

Originally Posted by tiannaleft

Thank you so much for this! When I say duplications, I mean that the data will stay on the first tab and when I go back the next day and enter more information and then add the date and run the macro, I don't want the data from yesterday to be copied and pasted into the second tab again since it should already be there.

This may be a bit more difficult. I can only think of two workarounds for that:

1. Insert a column so that you can enter a single character that will tell the macro that it has already been copied
2. Copy the data anyway, and then delete all of the duplicates in the second sheet

Originally Posted by tiannaleft

Also, I apologize for my ignorance in this as I really have no formal knowledge of Excel. my boss asked if I could get this done so I am doing my best. If I copy and paste your code into a new module in Visual Basic, will I then be able to key control+Z and have it work or is there something else I will need to do?

Open a new module, paste that code, and save the workbook. Then open the list of macros, select the new macro, and then click options. You should be able to enter the hotkey there.

Originally Posted by arlu1201

Another option is - clear the destination sheet before the transfer of data and then copy everything from the source sheet. That way, you will not have duplicates.

That is a great idea! There is another moment of "why didn't I think of that" haha.

If we want to do that, then this code should work:

Please Login or Register  to view this content.

ETA....

Just saw your new code, let me try it an get back to you...

Ah, well I definitely love the idea of that code but unfortunately need all of the info that it is the second to remain there as I am putting this code into data that is already existing. I suppose I could copy all of the data back onto the first sheet and could always hide it from view.....yes, I will try that.

Thank you sooooooo much!

Tiannaleft,
the scenario you describe was my first suggestion in post #5. This is possible, just give me a minute to have the code.

Please Login or Register  to view this content.

You may have to change the column where the "Y" is entered.

Thank you so much for that code and for your idea (I knew I got it from somewhere!) but unfortunately it isnt working properly. I did have another thought. After I run the original marco to copy and paste, would it be possible to create a separate marco that is very similar but that will CUT and then paste the items onto a 3rd tab? I used the following marco but I get an error message stating multiple items cannot be used in this macro.

Please Login or Register  to view this content.

I'm afraid you have lost me... The macro I posted worked with the sample sheet you posted. Can you tell me more about why that macro didn't work for you?

With that macro you just posted above - the error is telling you that you need to call it something other than "Copier()". Just rename it to "Copier2()" and make sure that that there is actually a third sheet.

I apologize for not being clear. I want a new macro that will take the same information from sheet one and cut it and paste it into sheet 3. So I will first run the original copy macro and then a second macro that will then cut those same things I just copied and paste them to sheet 3. The point of it being, I dont want to completely get rid of the information on sheet one but I need sheet one to be emptied once I run the copy/paste macro. Sheet 3 will then just be a holder/back up for me just in case I ever need to refer to the information.

When I run this macro for cut and paste, the error message I get is: Run time error 1004. The command you chose cannot be performed with multiple selections. Select a single range and click the command again.

Here is the code.

Please Login or Register  to view this content.

oh, ok. If that is the case, just use the same Copier macro, but change your sheet to sheet 3, and then put an additional line in to clear the contents of the first sheet if they were copied:

Please Login or Register  to view this content.

The code that I posted never hides a row, it just autofilters the list. So, you can just go to the filter in column A, and clear the filter - or you can add the following line of code to the macro after the main stuff is done:

Please Login or Register  to view this content.

Thank you. That worked perfectly. Thanks again for all your help!!