using a macro to copy cells from one sheet to another

[Tony]

I have a worksheet that uses vlookup to read values from a
input sheet based on date. From the summary sheet I want
to use the date entered and copy a row of cells to another
sheet where the dates are in column A and paste the values
of the coppied cells according to the date entered on the
summary page. When pasting the data I want to use the
value function in order to preserve the data as it is
calculated based on the specific date entered. I want to
use a option button and assign the macro to it so when I
click on the button the cels are coppied to the associated
date on the other sheet within the workbook. Any input on
this would be greatly appreciated.

Tony

 

[DavidC]

You need to find the rows where the date in one sheet
matches the date in the other sheet. For this you need
the find command as below:

With ActiveSheet.Range("A1:A500")
Set c = .Find(date variable, LookIn:=xlValues,
lookat:=xlWhole)

If Not c Is Nothing Then

then all the copy and paste commands. The paste command
needs to paste values. such as
ActiveSheet.Range(the range of cells to be copied).Copy

sheets("name of sheet to take the new data").range(cell
address for where to paste the data).PasteSpecial
paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End With


Hope this points you in the right direction.
BOL
DavidC

 

[Tony]

Thanks David I will give it a shot.

Toony

-----Original Message-----
You need to find the rows where the date in one sheet
matches the date in the other sheet. For this you need
the find command as below:

With ActiveSheet.Range("A1:A500")
Set c = .Find(date variable, LookIn:=xlValues,
lookat:=xlWhole)

If Not c Is Nothing Then

then all the copy and paste commands. The paste command
needs to paste values. such as
ActiveSheet.Range(the range of cells to be copied).Copy

sheets("name of sheet to take the new data").range(cell
address for where to paste the data).PasteSpecial
paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End With


Hope this points you in the right direction.
BOL
DavidC from
.