Update vaules only with Macro
set rng = Sheets("KanbanOrders") _
.Cells(rows.count,1).End(xlup)(2)
finds the next open cell in the sheet KanbanOrders by looking at column A.
It Column A isn't the right column to make this determination then you will
need to make an adjustment in the macro.
I can't see your sheet or your data - so I can only go by what you tell me.
for example you said:
when I recorded the macro I did a paste special with vaules only.
yet in your previous post, your code was clearly
ActiveSheet.Paste
which is not a pastespecial values.
in you previous code, you copied
Range("A2:M395").Select
so my assumption was that there was data in the last row in column A (even
if you had copied some unused rows). If this is not the case, then you have
to say what the case it.
If you want to paste in rows 2, 397, 792, 1187, etc on successive days, then
so state.
--
Regards,
Tom Ogilvy
"Barbara" wrote in message
...
Ok, it works but not the way I need it to. Maybe I didnt explain it very
well.
3 - sheets in workbook
1 for archiving, 1 for data entry, 1 source sheet
The user will enter part numbers in one col, many rows. By entering the
part
number it has a formula that is pulling in data automactically from the
source sheet.
I want her to archive the information automactically from the data entry
sheet to the archiving sheet after data entry for the day is completed,
because I will be sending her a new source sheet daily. Revision letters
and
other info will change under the part numbers. But the part numbers will
stay
the same.
This means there will be a different row for the "copy and paste" data to
go
to everytime.
Right now the macro is taking a picture of the data entry sheet and
pasting
values over to the archive sheet as it is for the day, but it is
overwriting
what was pasted the day before. I need to be able to keep the old entries.
I know for a computer savy person, they would highlight what they entered
for the day, select the cell in the archive sheet they want it to paste in
,
right click and paste special/ vaules only.
I was just wanting to automate it for her to make it easier.
Is there a way to do that?
I am sorry I may have mislead you with my poorly recorded macro.
Barbara
"Tom Ogilvy" wrote:
Sub Copyandpaste()
'
' Copyandpaste Macro
' Macro recorded 7/29/2005 by BSchwarz
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim rng as Range
set rng = Sheets("KanbanOrders") _
.Cells(rows.count,1).End(xlup)(2)
Range("A2:M395").Copy
rng.pasteSpecial xlValues
End Sub
--
Regards,
Tom Ogilvy
"Barbara" wrote in message
...
Anyone???
"Barbara" wrote:
It's working, but bringing over formulas, when I recorded the macro
I
did a
paste special with vaules only.
Can you make it do that?
thanks for you help,
Barb
"Tom Ogilvy" wrote:
Sub Copyandpaste()
'
' Copyandpaste Macro
' Macro recorded 7/29/2005 by BSchwarz
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Dim rng as Range
set rng = Sheets("KanbanOrders") _
.Cells(rows.count,1).End(xlup)(2)
Range("A2:M395").Copy Destination:=rng
End Sub
--
Regards,
Tom Ogilvy
"Barbara" wrote in message
...
I have a source sheet ( which will change often) and a data
entry
sheet
will
will be updated almost everyday. When the source sheet changes
it
will
change
vaules in the data entry sheet.
I want to extract the vaules only out of the data entry sheet
every
time
it
is updated into a main archive sheet in a user friendly way.
All sheets staying in the same workbook.
I recorded a macro to copy and paste the same rows and columns
out
of the
data entry sheet and into the archive sheet. But the vaules
paste
into the
same location everytime.
I want the user to click into a row of their choice (next empty
one)
and
click on a macro button to paste all new vaules into the
archive.
What do I need to change in this macro to make it work ..
Thanks,
Barb
Sub Copyandpaste()
'
' Copyandpaste Macro
' Macro recorded 7/29/2005 by BSchwarz
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Range("A2:M395").Select
Selection.Copy
Sheets("KanbanOrders").Select
Range("A2").Select
ActiveSheet.Paste
Range("N35").Select
End Sub
|