Need help...

[bdross03]

I am relatively new to VBA.

Here is what I have...

I have a Sheet1 that is my main information.
I have Sheet2 that has "filtered" information from Sheet1 and is used
to work from.

I need to take entered data from Sheet2 and copy that data to Sheet1.

I have Column A set up as an ID# on both sheets.
I need data from Columns O, P, Q on Sheet2 to copy to Columns Q, R, S
on Sheet1 matching the specific ID#.

I am at a loss on how this should be done.

Any help would be very appreciated.

Brian

 

[Tyro]

You can do that in Excel without resorting to VBA.
On sheet1 put the following formulas:
In Q1: =INDEX(Sheet2!O:O,MATCH(A1,Sheet2!A:A,0))
In R1: =INDEX(Sheet2!P,MATCH(A1,Sheet2!A:A,0))
In S1: =INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!A:A,0))
And drag the formulas down columns Q, R and S

 

[bdross03]

Thanks. that worked.

I have one remaining question that I forgot to add earlier.

How would I do this if the Sheet2 is "added" a later time?

Basically, Sheet2 is created during a "filter" action. People work
from Sheet2 entering information into columns O,P,Q.
I have Sheet2's name set up as a string and placed ina cell for
reference. How can I use that to do what you are suggesting?

 

[Tyro]

I don't understand your question. If you know the name of Sheet2, just put
the name in the formulas. Does the name of the sheet change?

 

[bdross03]

Yes.

Sheet2 in this case is created after the filter is applied (through
VB). Sheet2 is renamed for a specific month upon creation. The name of
Sheet2 is held in a cell on Sheet1.

 

[Tyro]

If the name of sheet2 is in cell B1 on sheet1 put the following formulas on
sheet1 in cells:

Q1: =INDEX(INDIRECT($B$1&"!O:O"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))
R1: =INDEX(INDIRECT($B$1&"!P"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))
S1:=INDEX(INDIRECT($B$1&"!Q:Q"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))

Drag the formulas down columns Q, R and S

Make sure the sheet name in B1 does not have spaces in it.

Tyro

 

[bdross03]

That worked!

Thanks!!!!

If the name of sheet2 is in cell B1 on sheet1 put the following formulas on
sheet1 in cells:

Q1: =INDEX(INDIRECT($B$1&"!O:O"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))
R1: =INDEX(INDIRECT($B$1&"!P"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))
S1:=INDEX(INDIRECT($B$1&"!Q:Q"),MATCH(A1,INDIRECT($B$1&"!A:A"),0))

Drag the formulas down columns Q, R and S

Make sure the sheet name in B1 does not have spaces in it.

Tyro








- Show quoted text -