Copying data between worksheets with ActiveX controls

s4lt25

New Member
Joined
Jul 29, 2014
Messages
4
Hey everyone,
I'm new to VBA and currently working on a project which requires me to regularly copy paste the same range of cells between different sheets.

I have a CommandButton which currently executes the following:
Sheets("04").Range("A2:B2").Copy Destination:=Sheets("05").Range("A2")
Sheets("03").Range("A2:B2").Copy Destination:=Sheets("04").Range("A2")
Sheets("02").Range("A2:B2").Copy Destination:=Sheets("03").Range("A2")
Sheets("01").Range("A2:B2").Copy Destination:=Sheets("02").Range("A2")
Basically I'm copy/pasting all data to the respective next sheet, which leaves Sheet 1 ready for new data input.

I want to modify this command in such a way that I can specify a certain value (e.g. 2) such that the command stops executing once this line is reached.
In plain text it would look as specified below:
CommandButton + TextInput Field
If TextInput("X") then stop command at Line X
Line Value4 Sheets("04").Range("A2:B2").Copy Destination:=Sheets("05").Range("A2")
Line Value3 Sheets("03").Range("A2:B2").Copy Destination:=Sheets("04").Range("A2")
Line Value2 Sheets("02").Range("A2:B2").Copy Destination:=Sheets("03").Range("A2")
Line Value1 Sheets("01").Range("A2:B2").Copy Destination:=Sheets("02").Range("A2")
What I want to achieve is the ability to move all specified data from sheet x to the respective next sheet x+1, while being able to specify when to stop without overwriting any existing data (the last sheet would always be empty).

I have more data than the specified range in the example, and also more sheets. What I need is a nudge on how to achieve the basic concept, I will later adapt it to suit my needs.
Thanks in advance for your help, I know that I didn't provide you with much - I just spent several hours trying a lot of non-working things, and it'd be great to be pointed in the right direction.

Cheers and thanks again
S
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

s4lt25

New Member
Joined
Jul 29, 2014
Messages
4
Might not have used to best header for this thread - anyways, if you see this, and know of any way or any resource to help me figure this out, your help will be much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,845
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top