Moving info from a worksheet to another using a button

[mattbird]

Hi,

I am trying to copy info from one worksheet to another when i click a button. Both worksheets have exactly the same lay out and range. The range I am trying to move from worksheet 1 is A1:O45 to worksheet 2 A1:O45.

Can anyone help me write a VBA for this?

Thanks

 

[Smitty]

Have you tried recording a macro?

 

[mattbird]

Hi,

I have try the following code which works but it copies the formulas and filters and colour. I would like it to only copy the figures in the cells if this is possible.

Sub Archive_Stats_TextBox1_Click()
Sheets("MATT 2 Stats").Range("A1:O45").Copy Sheets("PFA Archive").Range("A1")
End Sub

 

[kasanari]

Hi,

I have try the following code which works but it copies the formulas and filters and colour. I would like it to only copy the figures in the cells if this is possible.

Sub Archive_Stats_TextBox1_Click()
Sheets("MATT 2 Stats").Range("A1:O45").Copy Sheets("PFA Archive").Range("A1")
End Sub

Try recording the macro but copy / paste values the info on the new sheet instead of copying the entire sheet if you don't want the formatting and formulas.

 

[mattbird]

Im not sure on how to record a macro?

 

[My Aswer Is This]

Try this:

Sub Copy_Data()
Sheets(2).Range("A1:O45").Value = Sheets(1).Range("A1:O45").Value
End Sub

 

[mattbird]

It still copys all formulas and format.

 

[My Aswer Is This]

Mattbird.
Not sure who's script your using but the one I posted does not copy formats and formulas. I have tested this myself.

 

[Snakehips]

mattbird,

Was trying to respond to your 'second' thread but prevented.
mattbird,

Two possibilities, assuming that you have no need to copy anything other than values e.g. no formatting etc, Paste Special or just assign Value

Sub Archive_Stats_TextBox1_Click()
With Worksheets("Archived Stats")
    .Unprotect Password:="fiss"


Sheets("MATT 2 Stats").Range("A1:O45").Copy
Sheets("Archived Stats").Range("A2").PasteSpecial Paste:=xlPasteValues


   .Protect Password:="fiss"
    End With
End Sub


 'OR...


Sub Archive_Stats_TextBox1_Click()
With Worksheets("Archived Stats")
    .Unprotect Password:="fiss"


Sheets("Archived Stats").Range("A2:O46") = Sheets("MATT 2 Stats").Range("A1:O45").Value


   .Protect Password:="fiss"
    End With
End Sub

Hope that helps.

 

[My Aswer Is This]

I don't understand why my suggestion in post #6 above does not work. It should be obvious you could change Sheets(1) to Sheets("MATT 2 Stats") if you want.
In his op he did not give the sheet names.

If the op does not know how to use the recorder he/she may not know how to install the script.