Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Kay Kay is offline
external usenet poster
 
Posts: 129
Default Help to code Macro to Copy fron one sheet and paste in other sheet

Hi
I have tow excel sheets.- sheet1 and sheet2
in sheet2 i have all columns from sheet1 + additional columns.
in sheet1 i have unique Action# column which does not have dublicate data.
and that ACtion# is in sheet2 (as i have copied Sheet1 data).

So what i want when some one adds or update data of sheet1,
i want to run a macro which adds new data(rows) into sheet2 and updates data
as well (update means- if someone updates the data in sheet1, it should
update those data in sheet2 when you run a macro)

So need help to code macro which adds data in sheet2 and if data is there
then update it.

I appreciate for any help.

Thanks!


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,071
Default Help to code Macro to Copy fron one sheet and paste in other sheet

Kay
Help us out here. Pretend that you have to do this manually (no
automation). Now come up with all the different scenarios of the possible
conditions that you might have, things like the Action# exists in sheet1 but
not in sheet 2, the Action# exists in both sheets, any other scenarios you
know about. Then give us a step-by-step of how you would handle each
scenario. Don't leave out any steps. Nobody knows what you have and what
you want like you do. HTH Otto
"kay" wrote in message
...
Hi
I have tow excel sheets.- sheet1 and sheet2
in sheet2 i have all columns from sheet1 + additional columns.
in sheet1 i have unique Action# column which does not have dublicate data.
and that ACtion# is in sheet2 (as i have copied Sheet1 data).

So what i want when some one adds or update data of sheet1,
i want to run a macro which adds new data(rows) into sheet2 and updates
data
as well (update means- if someone updates the data in sheet1, it should
update those data in sheet2 when you run a macro)

So need help to code macro which adds data in sheet2 and if data is there
then update it.

I appreciate for any help.

Thanks!




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default Help to code Macro to Copy fron one sheet and paste in othersheet

On Jul 24, 4:11*pm, kay wrote:
Hi
I have tow excel sheets.- sheet1 and sheet2
in sheet2 i have all columns from sheet1 + *additional columns.
in sheet1 i have unique Action# column which does not have dublicate data..
and that ACtion# is in sheet2 (as i have copied Sheet1 data).

So what i want when some one adds or update data of sheet1,
i want to run a macro which adds new data(rows) into sheet2 and updates data
as well (update means- if someone updates the data in sheet1, it should
update those data in sheet2 when you run a macro)

So need help to code macro which adds data in sheet2 and if data is there
then update it.

I appreciate for any help.

Thanks!


Suppose you could always Vlookup the ActiveCell.value and have a macro
to do the following...

Right click on the sheet1 tab and goto View Code
Sheet


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False 'Turns off the flicker
ActiveCell.Offset(0, 1).Value = "=IF(ISNA(VLOOKUP(" &
ActiveCell.Offset(0, -1).Address & ", Sheet2!A:A, 1, FALSE))", "",
VLOOKUP(" & ActiveCell.Offset(0, -1).Address & ", Sheet2!A:A, 1,
FALSE))"
ActiveCell.Offset(0, 1).Select

If Len(ActiveCell.Value) = 0 Then
Sheets("Sheet2").Select
'DoYourDesiredActionGoesHere
Sheets("Sheet1").Select
End If

ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select 'Just jumps down to the next row

End Sub

The basic idea is a VLOOKUP statement will return a value if it's
found in a specific range of data. If it's found it means that the
data is there and nothing has to change. If the VLOOKUP comes back as
nothing you can get the code to do what you need it to. There are
seriously better ways of doing what your doing but they are far more
complicated then what I'm showing here. I hope this helps points in
you the right direction.
  #4   Report Post  
Posted to microsoft.public.excel.programming
Kay Kay is offline
external usenet poster
 
Posts: 129
Default Help to code Macro to Copy fron one sheet and paste in other s

Thanks to both of you.
this comments will help me.


" wrote:

On Jul 24, 4:11 pm, kay wrote:
Hi
I have tow excel sheets.- sheet1 and sheet2
in sheet2 i have all columns from sheet1 + additional columns.
in sheet1 i have unique Action# column which does not have dublicate data..
and that ACtion# is in sheet2 (as i have copied Sheet1 data).

So what i want when some one adds or update data of sheet1,
i want to run a macro which adds new data(rows) into sheet2 and updates data
as well (update means- if someone updates the data in sheet1, it should
update those data in sheet2 when you run a macro)

So need help to code macro which adds data in sheet2 and if data is there
then update it.

I appreciate for any help.

Thanks!


Suppose you could always Vlookup the ActiveCell.value and have a macro
to do the following...

Right click on the sheet1 tab and goto View Code
Sheet


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False 'Turns off the flicker
ActiveCell.Offset(0, 1).Value = "=IF(ISNA(VLOOKUP(" &
ActiveCell.Offset(0, -1).Address & ", Sheet2!A:A, 1, FALSE))", "",
VLOOKUP(" & ActiveCell.Offset(0, -1).Address & ", Sheet2!A:A, 1,
FALSE))"
ActiveCell.Offset(0, 1).Select

If Len(ActiveCell.Value) = 0 Then
Sheets("Sheet2").Select
'DoYourDesiredActionGoesHere
Sheets("Sheet1").Select
End If

ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select 'Just jumps down to the next row

End Sub

The basic idea is a VLOOKUP statement will return a value if it's
found in a specific range of data. If it's found it means that the
data is there and nothing has to change. If the VLOOKUP comes back as
nothing you can get the code to do what you need it to. There are
seriously better ways of doing what your doing but they are far more
complicated then what I'm showing here. I hope this helps points in
you the right direction.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy paste Macro in a Protected Sheet [email protected] Excel Discussion (Misc queries) 1 January 9th 09 03:16 AM
Macro to copy and paste to another sheet, based on if-then-else ABlevins Excel Programming 0 May 19th 08 02:09 PM
code to FIND value, copy, paste values onto other sheet ufo_pilot Excel Programming 2 December 6th 05 05:14 PM
Macro help - copy row, clear, and paste on different sheet miker1999[_3_] Excel Programming 3 February 1st 04 04:22 AM
copy and paste from different sheets into one sheet using a VB code reena Excel Programming 2 August 5th 03 02:49 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"