Looking for help with VBA macro

Closed
ecmike Posts 2 Registration date Thursday January 29, 2009 Status Member Last seen January 31, 2009 - Jan 30, 2009 at 04:16 PM
ecmike Posts 2 Registration date Thursday January 29, 2009 Status Member Last seen January 31, 2009 - Jan 31, 2009 at 01:53 PM
Hello,

I am trying to write a VBA macro in excel that will do the following:

When the values of the cells in the range B1-B10 of "Sheet 1" are changed (by entering value and pressing enter), the macro should copy the corresponding values from the cell range C1-C20 from "Sheet 2" into an empty row in "Sheet 3". Thus, every time I change the value in B1-B10 of "Sheet 1", the macro saves the data calculated in "Sheet 2" to a third worksheet and collects the saved data.

This is my attempt so far but I'm fairly new to VBA and have not been finding a solution:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
Sheets("Sheet 2").Select
Range("C1:C20").Select
Selection.Copy
Sheets("Sheet 3").Select
Range ("A1:A20")
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
End Sub

Any help or suggestions would be greatly appreciated! Thanks!

2 replies

undoneso Posts 8 Registration date Saturday November 15, 2008 Status Member Last seen May 19, 2009
Jan 31, 2009 at 01:56 AM
hi,
try this klink to get the user guide:
www.blackbaud.co.uk/files/support/guides/re7uk/vbaUK.pdf
you can learn everything you want.
0
ecmike Posts 2 Registration date Thursday January 29, 2009 Status Member Last seen January 31, 2009
Jan 31, 2009 at 01:53 PM
Thanks for the tip. I would appreciate any advice on the following:

Sub elaseval()
For each cell in Range("B7:B50")
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C" & cell.Row : "T" & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub

I am trying to apply this set of commands to each cell in the range B7:B50. For each cell that the commands are applied to, I need cell.Value to refer to the value in the cell and cell.Row to represent the row of the cell that is currently being used.

I would really appreciate any tips. Thanks.
0