MrExcel Publishing
Your One Stop for Excel Tips & Solutions

macro help


Posted by ken on February 06, 2002 4:05 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 Derek on February 06, 2002 5:29 AM

Ken,
This might help you get started: I have only covered the first step but the other steps are merely a repeat of the process.

Use the macro recorder to obtain the code (Tools / Macro /Record New Macro)
First select your range D3:D10 in Sheet 1 and type the = sign
Next go to Sheet 2 and select your range B3:B10, hold down control and press enter
Select Edit / Copy then select Edit / Paste Special / Values

Stop recording. Press Shift + F11 to go to the code, which will look like this

Range("D3:D10").Select
Selection.FormulaR1C1 = "=Sheet2!RC[-2]:R[7]C[-2]"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Add an if statement to the code like this:

If Range("A1").Value = 1 Then
Range("D3:D10").Select
Selection.FormulaR1C1 = "=Sheet2!RC[-2]:R[7]C[-2]"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Else
If Range("A1").Value = 2 Then
etc etc

If you have used 3 If Statements then you need 3 End If Statements at the end of your code, eg
End If
End If
End If

Hope this helps
Derek