macro help

Posted by ken on February 08, 2002 2:39 AM

How can I make a macro so that it can copy specific cell according to a value in another cell, and paste it to another cell?
I.e. in sheet 1 , cell A1, if the user type 1 in, then the macro go to sheet 2 and copy cells B3:B10 and paste the value to sheet 1 cells D3:D10 ,also copy sheet 2 cell C3:C10 and paste the value to sheet 1 cell H3:H10.
If the user type 2 in cell A1, then when he ran the macro, it will copy cells D3:D10 and paste the value to sheet 1 cells D3:D10, also copy sheet 2 cell E3:E10 and paste the value to sheet 1 cell H3:H10 and if type 3, then it will copy F3:F10 and G3:G10 and so on
Any hep will be appreciate

Posted by Seleucus on February 08, 2002 3:40 AM

Put this in the Sheet1 code module :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric([a1]) Then
If [a1] - Int([a1]) = 0 Then Copy_Paste
End If
End Sub

And, put this in a normal module :-

Sub Copy_Paste()
Dim ws1 As Worksheet, ws2 As Worksheet, c As Integer
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
c = ws1.[a1].Value * 2
ws2.Range(ws2.Cells(3, c), ws2.Cells(10, c)).Copy ws1.[D3]
ws2.Range(ws2.Cells(3, c + 1), ws2.Cells(10, c + 1)).Copy ws1.[H3]
End Sub

Posted by ken on February 08, 2002 4:05 AM

thx it worked
but how can i make it so that it only paste the value?

Posted by Seleucus on February 08, 2002 5:28 AM

Sub Copy_Paste()
Dim ws1 As Worksheet, ws2 As Worksheet, c As Integer
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
c = ws1.[a1].Value * 2
ws2.Range(ws2.Cells(3, c), ws2.Cells(10, c)).Copy
ws1.[D3].PasteSpecial Paste:=xlValues
ws2.Range(ws2.Cells(3, c + 1), ws2.Cells(10, c + 1)).Copy
ws1.[H3].PasteSpecial Paste:=xlValues
End Sub