Following on from yesterday, I still haven't quite got the solution.
Because I may have not explained myself, sorry.

A B C D E F
1 2 Y N 6 6 8
2 2 N N 8 9 9
3 3 Y Y 6 7 3
4 3 N T 7 5 2
5 3 Y Y 5 7 6

In the above WB called rejects I have the following information. A1:A5
are week numbers. In WB called reports, I want to put a week Number in
A1 i.e 3. Then open WB rejects and select all data A3:F5, representing
all data for week 3. Then to copy it and paste is in WB reports from
cell A2. Then close WB rejects. Just to complicate matters, I would
like to paste the data using (Paste Special, values option.

the simplest solution is to record a macro and then say a variable holds the
value that you enter in the wb record, and use that variable in the recorded
macro instead of a static value.

like:,
dim hold as integer
hold = workbooks(reports).worksheets(sheet1?).range(A1).v alue
and now, use hold in the recorded macro..


"Decreenisi" wrote:

Following on from yesterday, I still haven't quite got the solution.
Because I may have not explained myself, sorry.

A B C D E F
1 2 Y N 6 6 8
2 2 N N 8 9 9
3 3 Y Y 6 7 3
4 3 N T 7 5 2
5 3 Y Y 5 7 6

In the above WB called rejects I have the following information. A1:A5
are week numbers. In WB called reports, I want to put a week Number in
A1 i.e 3. Then open WB rejects and select all data A3:F5, representing
all data for week 3. Then to copy it and paste is in WB reports from
cell A2. Then close WB rejects. Just to complicate matters, I would
like to paste the data using (Paste Special, values option.

Here's a change-event macro that may do just what you want.........
Put it in the Worksheet module of the sheet you want it to work on and enter
your week number in A1.....it will fill in the data in B1:G1

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Range("b1").Formula = "='c:\[rejects.xls]sheet1'!b" & Range("a1").Value
Range("c1").Formula = "='c:\[rejects.xls]sheet1'!c" & Range("a1").Value
Range("d1").Formula = "='c:\[rejects.xls]sheet1'!d" & Range("a1").Value
Range("e1").Formula = "='c:\[rejects.xls]sheet1'!e" & Range("a1").Value
Range("f1").Formula = "='c:\[rejects.xls]sheet1'!f" & Range("a1").Value
Range("g1").Formula = "='c:\[rejects.xls]sheet1'!g" & Range("a1").Value
End Sub

Macro written in XL97

Vaya con Dios,
Chuck, CABGx3


"Decreenisi" wrote:

Following on from yesterday, I still haven't quite got the solution.
Because I may have not explained myself, sorry.

A B C D E F
1 2 Y N 6 6 8
2 2 N N 8 9 9
3 3 Y Y 6 7 3
4 3 N T 7 5 2
5 3 Y Y 5 7 6

In the above WB called rejects I have the following information. A1:A5
are week numbers. In WB called reports, I want to put a week Number in
A1 i.e 3. Then open WB rejects and select all data A3:F5, representing
all data for week 3. Then to copy it and paste is in WB reports from
cell A2. Then close WB rejects. Just to complicate matters, I would
like to paste the data using (Paste Special, values option.