Copying a column to another column

[Scary123]

Hi everyone,

I have a workbook where I want to be able to use a command button to select which will move data from 1 column to another in the same worksheet.

The following is what I am looking for: -

1. Copy Data that is in Column C4:C23 [Range Name is Calculations]
2. Paste Data into column A4:A23 while using paste special (Values) [Range name is Copied_Cells]
3. The Sheet name is called (Calculations)

I can do a form control and record the macro but when I change the file name I have to redo the macros.
I want to be able to change the file name and have the button work without re-doing the macro.

My file is a football league and the only thing that I want to do is get this button working if it is possible.

Mark

Football League.xlsx
	A		C
3	Positions		Rank
4	13		11
5	5		8
6	17		17
7	16		16
8	6		9
9	15		14
10	4		7
11	18		18
12	11		12
13	3		3
14	1		1
15	8		5
16	2		2
17	14		15
18	20		20
19	9		6
20	7		4
21	19		19
22	10		10
23	12		13
Calculations

 

[Fluff]

Is the button on the same sheet as the data?

 

[Scary123]

Yes it needs to be.

 

[Fluff]

In that case you can use

Sub Scary()
   Range("A4:A23").Value = Range("C4:C23").Value
End Sub

 

[Scary123]

In that case you can use

Sub Scary()
   Range("A4:A23").Value = Range("C4:C23").Value
End Sub

The scary in the Sub line does that stay as Scary or does that need to be the name of my workbook?

 

[Fluff]

No that's just the name of the macro, you can change it to whatever you want & assign it to your button.

 

[Scary123]

No that's just the name of the macro, you can change it to whatever you want & assign it to your button.

Unfortunately it does not work I have selected the active X control in development and clicked on the button then selected the area in the sheet and put the data in the VBA box
as below: -
Sub Scary()

Range("C4:C23").Value = Range("A4:A23").Value

End Sub

But when I click it I get the six circles around it then if I select the c column which is the column to be copied the button goes to a normal one but then when I select the button
nothing happens.

I'm probably doing something stupid.

Mark

 

[Fluff]

Get rid of the ActiveX button & use a Form control button instead, then simply right click the button & assign macro, then select the macro from the list.
Also the macro needs to go in a standard module & not the sheet module.

 

[Scary123]

Get rid of the ActiveX button & use a Form control button instead, then simply right click the button & assign macro, then select the macro from the list.
Also the macro needs to go in a standard module & not the sheet module.

Ive done that and went through the recording and it went to the module1 as follows: -

Sub CopyScary()
'
' CopyScary Macro
'

'
Range("Calculations[Rank]").Select
Selection.Copy
Range("A4:A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

It works in the one file but when I save it as another file under a different name I have to go through the same procedures of recording the macro which is what I was trying
to get out of.

Mark

 

[Scary123]

Ive done that and went through the recording and it went to the module1 as follows: -

Sub CopyScary()
'
' CopyScary Macro
'

'
Range("Calculations[Rank]").Select
Selection.Copy
Range("A4:A23").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

It works in the one file but when I save it as another file under a different name I have to go through the same procedures of recording the macro which is what I was trying
to get out of.

I replaced the details in the module1 with the following: -

Sub Scary()

Range("C4:C23").Value = Range("A4:A23").Value

End Sub

But everytime I press the button it comes with Cannot run the macro it is not available in the workbook.

Mark