MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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