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