Assigning macro to Buttons in a Sheet

[Howardiser]

Hi All,

I was assigned to create a simple button macro used in Excel 2003. As I'm new to VBA, it took me a while to come up with a workable code.

Refer to the code below.

Please Login or Register  to view this content.

As stated above, I have two worksheets, ListSheet, and PasteSheet. The above coding works in PasteSheet when I assigned it to a button there. However when I assigned the code to a button in ListSheet, an error code of 400 appeared.

Basically, what does this button do is to copy a certain range of cells from ListSheet to a range of cells in PasteSheet. If the cells in PasteSheet are occupied, it will shift down and paste the copied cells on the new group of cells.

I have also planned in advance such that I will be creating more buttons for other cells. I'm wondering whether there is a code such that I don't need to recreate for different buttons via hyperlinking it to the cells I need it to copy.

Thanks!

[Howardiser]

Alright, just a slight update. Google search can be pretty helpful when you stress test it with enquiries.

I managed to create a workable button code. Refer below:

Please Login or Register  to view this content.

The issue now I need to create alot of such buttons and codes for different range of cells. Is there a way where I can use just one button code to run them universally?

Thanks!

[royUK]

That code is very inefficient. Attach an example workbook

[Howardiser]

Hi royUK,

Thanks for replying back.

I realized I had missed out a line in the previous post. Thus as attached the sample workbook.

I will edit the previous post as well.


Thanks again.

[royUK]

You don't need to activate the sheets, you don't need to use the loops - both will make the code slower.

Exactly what do you want to copy & where to?

[Howardiser]

Sorry about the delay in replying due to time zone difference.

What I'm copying is:

A cell of 'Item description'

A cell of 'Part No."

A cell of "Price", which means in numerical form.

I'm basically just copying a certain number of cells from one sheet and pasting them on another sheet which is a list using a button. My reason for looping is because I will need to create more such buttons for other entries.

Also why I need to activate the sheets is because the code initially will not run if the sheets are not activated. Anyways, I'll study the codes further and see what I can improve on.

Thanks.

[Howardiser]

Another update.

Thanks to royUK for the pointers.

Please Login or Register  to view this content.

I've removed the initial screen activation since I'm already at the screen itself.

Also removed the loop at the bottom, realising that its just doing unnecessary loops.


Thanks.