Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update vaules only with Macro
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 "Tom Ogilvy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and add vaules | Excel Worksheet Functions | |||
If no value in a1 how do I get a2 a3 a4 a5 a6 a7 a8 not to show their vaules | Excel Worksheet Functions | |||
Truning 0 vaules red | Excel Worksheet Functions | |||
unique vaules | Excel Programming | |||
Update info in a worksheet dinamically when a drop box change its vaules | Excel Programming |