Macro - please help with VB Code

[bh9aq]

Hello,
I'm working with two seperate workbooks in Excel 2000, The main workbook where My Macro is, is a data collection spreadsheet looking up some values from the second workbook. There is a unique code in one of the cells of the main workbook which looks up the values. I want the macro to run through the list on 2nd workbook looking up the unique code and updatting the main workbook and each time saving the file as it goes along. I've got the code for it to save but I need it to run through the list automatically.

The code is as follows:

[vba]Public Sub SaveAs()
Const PATH As String = "O:\P&r\Resource\Statistics\"
With ThisWorkbook
.SaveAs Filename:=PATH & _
.Sheets("Key Stage 2").Range("B4").Value & ".xls"
End With
End Sub[/vba]
Please could someone help as I don't even know the basics of Visual Basic!

Many Thanks
Homara

[mdmackillop]

Hi Homara,
Welcome to VBAX
We really need to see your layout to assist with this. Can you remove any sensitive data, zip your wotrkbooks and post them using Manage Attachments in the Go Advanced sections
Regards
MD

[bh9aq]

Thank you for responding. Attaching the two workbooks now.

Like I said, I'm working on two separate workbooks. Cell K1 in Workbook1 LOOKSUP values from Column A of Workbook2. At the moment I'm changing the value of Cell K1 (workbook1) manually but I want the Macro to do that as the list in workbook can be long. Macro should run from the first value in column A(workbook2), Select it in Cell K1(workbook1) and save this workbook giving it a name from a cell in this workbook.

Then I want Macro to go back to Workbook2 choosing 2nd Value in Column A, updating K1(workbook1) and saving the file again and doing the same until the next cell in ColumnA(Workbook2) is blank. I've got the code for it to save at my desired location with th desired name but I can'y=t write the LOOP code to make it LOOKUP the values.

I hope it makes sense.

Regards

[acw]

Homara

Try this

[VBA]
Public Sub SaveAs()
Const PATH As String = "E:\P&r\Resource\Statistics\Key Stage performance Data\Key Stage Results 2006\Key Stage 2 2006\Provisional KS2 results 06\"
Set DataWS = Workbooks("workbook2.xls").Sheets("sheet1")
For Each ce In DataWS.Range("A2:A" & DataWS.Cells(Rows.Count, 1).End(xlUp).Row)
With ThisWorkbook
.Range("K1").Value = ce.Value
.SaveAs Filename:=PATH & _
.Sheets("Key Stage 2").Range("B4").Value & ".xls"
End With
Next ce
End Sub
[/VBA]

Didn't test it, but hope its ok.


Tony

[bh9aq]

Tony,

Many Thanks for your response. I tried to run the code but it comes with the following error:

Compile Error:
Method or Data Member not found

Not quite sure, why and How!

Thanks for your help!

Regards

Homara

[mdmackillop]

Hi Homara,
You're missing a sheet reference between the book and the cell address
eg
[vba]
With ThisWorkbook.Sheets(1)
.Range("K1").Value = ce.Value
[/vba]