Copy sent to "History Sheet" everytime the Active X button is pressed.

Hello,

I am looking for cells B18, C18, and E18 on Sheet 1 to be sent to the History sheet to the first available open row when the Box Count button is pressed. The first available row starts at row 3 and moves down depending on how many entries are already on the page. Any help would be appreciated. I'm not very good with this part of vba coding.


Thanks


The code is in module 1 as Sub Boxcount. Here is the code.

Please Login or Register  to view this content.

Is anybody awake this morning that is good with this sort of thing? I tried to figure out how it was being posted to the history sheet by my other command buttons, but I had no luck.

Okay, I made one change to the range I was sending to the history sheet. I just unmerged a cell, so the range is now B:18 to D:18.

I wrote a code that is doing what I asked, but it doesn't work like the other code that was written for me. This code flashes to the "History Sheet" and then back to "Sheet 1". That does not with the original coding in the Inventory active x button.

If anyone can modify this code so it is more efficient without the quick flash to copy and paste the information to the "History Sheet", I would be interested in learning.

Thank you for any help in advance.

Final Code for now

Please Login or Register  to view this content.

This is way to easy. See the highlighed fonts added to the code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have been working on another way to send it to the "History Sheet" similar to the way it was sent with the other Active x buttons but it keeps giving me an error and tripping out the please select a row message.

When I am selected in any of the three boxes it will send the copy to the history sheet, but when I am selected somewhere else on the sheet it will not send a copy to the history sheet.

Either way the new code keeps stopping on the bolded area.

That's because the code requires the active cell to be one of the cells that contain the dropdown box in column A.

So, yes, if the active cell is other than that, the code 'fails' and shows the message box to select a size or to select a row (depending on the button pressed).

I believe your problem lies with the highlighted section of the code below. The line 'With.ActiveCell' needs to be changed.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This is the code I have come up with for now. It does work, but it will work from anywhere the selected cell is. I cannot find a working error code to let me know the selected cell is not one of the three cell B18, C18, or D18....

There are two different codes here for copy and pasting to another sheet. Neither one are quite right yet, so I am looking for the copy and paste experts.

With the first one,

Please Login or Register  to view this content.

this part of the code needs to paste special and just transfer the values. Right now it is still carrying the original format and I would like it to just transfer the values.

With the second one, I still haven't found a way for the code to make sure it is selected on the group of cells copying to the History Sheet.

Add the highlighted part to the end of your code;
I believe this will do what you need.

Please Login or Register  to view this content.

If that doesn't do everything you need, then try this;

Please Login or Register  to view this content.

I couldn't get either one of those codes to work this morning. I will investigate it more after work today.

Please Login or Register  to view this content.

This is the code I am using to copy my entry to the History Sheet in my workbook from sheet1. I am looking to find a way to identify the active cell or cells in range R1 to copy to the History Sheet or have a message box come up and say "please select row." It is likely I will add another small range underneath it (as shown on sheet1 in my example) so I will need to distinguish between the two.

Thanks for any help.

Originally Posted by Spreadlover

This is the final coding I came up with. Now I will be able to add other items to track on sheet1 as long as they are in the B, C, and D columns. Only the row selected will be active and I did not have to name a specific cell for the subtraction or the copying to the History Sheet.

Thanks for the help!!

Please Login or Register  to view this content.

If you look in module 3, you will see this code and a whole other way to copy and paste to the another sheet.

A few things were change around since I started this thread, but this is the final code I came up with.