data from one worksheet to another

[Garima15]

I have a scenario, worksheet 1 column A gets its data from worksheet2 column A, I have linked the two so that's easy, but if I delete or add a row in between worksheet 2 that doesnot get reflected in worksheet1, how do I achieve that, TIA.

[#2]

@Garima15 

Getting data from one to another sheet can be better handled by Lookup formula either VLOOKUP or INDEX & MATCH,,, in case of delete you may wrap these with IFERROR.

Another is Paste Link,,!

Reply in case of further assistance, and better share the WB with us along with formula/method U r trying so far.

[#3]

@Rajesh_Sinhathanks for replying, actually my requirement is very simple, vlookup is good for match n verify but I need to fill 3 worksheets of similar nature from one point, I have attached excel snapshots, where 1. file has 3 months activities for each month (dec-2020,Jan-2021...), the first column has all the activities listed for that month.

2. a worksheet I want to create such that first column of every month can be filled, updated through this one sheet otherwise any addition or deletion of activities needed to be updated for all 3 months one by one.

3. adding a row in ActivtyList tab shd reflect in all three

4. similarly for deleting a row

to fill the first column for all months, I am linking the column A to ActivityList columnA using paste link

hope there is simple scripts available to do this task since its not going to be huge data just a regular updates.

Thanks,

Garima.

[#4]

@Garima15 

The most easiest is INDEX formula:

Enter this in cell A2 of destination sheets, and copy down till needed.

=INDEX(ActivitySheet!$A2:$A2,1)

• As soon Activity Sheet's column A finds any change either Delete or New data or Updates all destinations sheets reflect them.

Another is VBA macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, Lastrow As Long

If Target.Column = 1 Then


    Lastrow = Sheets("ActivitySheet").Range("A" & Rows.Count).End(xlUp).Row
    
    Sheets("Destination1").Range("A2:A100").ClearContents
    
    For i = 100 To Lastrow
        
    Sheets("ActivitySheet").Cells(i, "A").EntireRow.Copy _
  
     Destination:=Sheets("Destination1").Range("A" & Rows.Count).End(xlUp)

        End If
    Next i
End If

Range("A1").Select
End Sub

• Save this as Standard Module.
• Save the WB as Macro Enabled *.xlsm.
• You need to write this code for every destination Sheet, where you want to copy value from Activity Sheet.

[#5]

@Rajesh_SinhaThanks for the pointers as in VBA code, I am using that to achieve my requirement.

[#6]

@Garima15 

I might suggest that you use the following formula in column A of all created sheets:

=INDEX(INDIRECT("ActivitySheet!$A"&ROW()):INDIRECT("ActivitySheet!$A"&ROW()),1)

I am thinking that if you have a sheet called "ActivitySheet", and in the first column A of that sheet called "ActivitySheet" you list all the activities on each row in that column A

THEN

in the other created sheets, you put the above formula in each row of column A,

then column A of all the created sheets will have the same as column A of the Sheet Called "ActivitySheet"

So that means that you can add or delete any row in the "ActivitySheet" and the created sheets will all have the same data in column A as is in column A of the "ActivitySheet".

[#7]

@Garima15 

Happy to learn that the VBA code is working for you,,, now if you feel you may accept my post as Best Solution as well Like.

Keep asking in case any other issue.