Update vaules only with Macro

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

Anyone???

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]

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

 

[Tom Ogilvy]

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.

 

[Guest]

Ok, I am sorry, I thought I had recorded a paste values, I guess I didnt.

Also My goof was "Yes" there will be data in Col "A", but I forgot to put
anything in that col. I was only testing with date coming into other col. See
Col will be entered manually and I was only testing what was comming in
through a VLOOKUP.

It works fine,Thank you very much you have been extreamly helpful,
Barbara