Copy Range from Sheet2 to Sheet1 through VBA

[FallingDown]

Hi everyone

I am sure it's really simple, but even through Google I did not manage to come across a helpful Code for the life of me

What I basicly need is that Excel copies the Range B:I from Sheet2 to Sheet1 whenever data is entered in Sheet2.

Note that Sheet1 will be hidden for the user.

At the same time the value for A:A in sheet1 should be filled in by looking up the value of A:A in Sheet2 and replace it with the value defined in Sheet3
(allthough once I know how to get the first part this will be easy)


Edit: I should note, that if a user changes the value of any cell, the value in Sheet1 should change as well

Anyone who could help?

Thanks
FD

[FallingDown]

Hi all

OK, so as I thought, fairly simple

Please Login or Register  to view this content.

BUT: How would I exclude Col. A, or limit the code to col B to I?

[arlu1201]

You can try this - in the sheet1 code window

Please Login or Register  to view this content.

Though this will copy info even if one column is populated, we will need to send a condition that only once a particular column is populated (maybe column I in your case), then the transfer should happen.

[stojko89]

how do you want this code to work?
When some one types anywhere in sheet2 the whole row from column B to I gets copyed to Sheet 1?
Or do you mean when some one types something in column A the same row column B to I gets copyed to sheet 1 where there is the same value?

It could be much easier if you uploaded an example workbook with a before and after situation.

[FallingDown]

Hi Arlette

It ends in runtime error

--> Shouldnt I be able to define the range in my code beforehand?

[arlu1201]

There was a typo there. I corrected my previous post.

What do you mean by define the range? As per the code, the data from B to I of the target row will get copied starting in column B in the target row of Sheet2.

[FallingDown]

Hi stojko89

Whenever a user enters a value in range "B:I" (any row) it should copy the value to the same target, just on sheet1
So if a User enters "Hello" in Sheet2 to for Cell B4, "Hello" should also appear in Cell B4 on Sheet 1

The code I posted does exactly that, but for the entire worksheet(s).
This would not be a problem, would it not be for the fact that I will need different values for col. A in Sheet1

[stojko89]

Never mind my post... arlu1201 beats me every time
You just need to set the Intersect the target to only copy if the target value changes in the range of B:I and not if you type a value in column A and you'll be good to go

[stojko89]

So like this

Please Login or Register  to view this content.

[FallingDown]

Thanks to both of you: works great

[arlu1201]

Am glad its solved.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

I will do it for you this time.

[FallingDown]

Hi Arlette

As said, your code works perfectly fine.
BUT: Is it possible to have the VALUES copied.
Cause your copies the formula as well

Thanks

[stojko89]

Try using this then:

Please Login or Register  to view this content.