Insert row: entered value in columnB copied into another worksheet.

Hi,

I have created a button called "Insert line". When this is clicked, it inserts a row in the same work sheet and in a different work sheet Column E is coped to the next empty colum( ie Column E get coppied into F then to G, H, I when the button is clicked). This part is working.

What I now want to do is whatever value I entered in column B of the newly inserted row, I want this to be copied into the other worksheets copy-paseted columns row 5. (ie. Say I clicked the button and Row 6 is inserted, If i enter "Site A" in B6, then I want "Site A" to appear in the column I created in the other work sheet eg "F5". when I click the buton again, now Row 7 is inserted. So i enter "Site B". I want this to be copied into other worksheets F6.

This is the code I have

Please Login or Register  to view this content.

Any help is much appriciated.

Hi tharindudk,

It is very difficult to visualise what you are trying to do without seeing your workbook.

This is further complicated by the fact that you are grabbing some data from another workbook.

It seems to me that you want to enter something into the newly created row.

I think you need to have something like cells(5,6) = ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[-4]"

This will cause the value in Sheet1!B6 to appear in F5. (F5 will have the formula =Sheet1!B6

I hope this helps!

David


Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi,

Test1.xlsm

I have attached a template.

If you click the button "Insert Store" in sheet 1. It creates a new row and manualy need to enter Store Name in column B. Eg B6 you Enter "Melbourne"

At the same time, a column was copied and created in sheet 2. Here the columns start from F and you can see F4 is named as F4. I want this to be changed as the name entered in B6 - Melbourne.

When again the button is created, B7 is created and when user name it, i want G4 to change the Store name to that. this should happen every time a user click that button.

Any help is appriciated.