Saving a document sequentially

[P.Douglas]

Hi,

I am a beginner with VBA and wondering if anyone could help me.

I need to copy a cell, (say from Cell A1, Sheet 1 of Workbook 1) to replace the contents of another cell in another workbook (say Cell A100, Sheet 1 of Workbook 2) which has both text and numbers in the same cell (i.e. This Value = 123) - I only want to replace the numbers and leave the text and the equals sign in tact.
I then need to save the modified Sheet 1 of Workbook 2 as a .txt file, called for example, 'run1'.
Then I need to copy the next cell down (Cell A2) from Sheet 1, Workbook 1, and paste into the same cell (Cell A100) in Sheet 1, Workbook 2, And then save this sequentially, so as 'run2'.
I need to do this hundreds of times until I run out of values in Sheet 1, Workbook 1.

I hope I have tried to explain my problem as clearly as possible. I have done web searches but haven't had any success.

I would appreciate any help anyone could give me.

Kind Regards

P.Douglas

 

[jbeaucaire]

Assuming the form sheet is already open, edit the reference to it at the top of this macro, then give it a try. This macro goes in wb1.

Option Explicit

Sub FillOutForm()
Dim wsFrm As Worksheet:     Set wsFrm = Workbooks("Form.xls").Sheets(1)
Dim wsData As Worksheet:    Set wsData = ThisWorkbook.Sheets(1)
Dim RNG As Range:           Set RNG = wsData.Range("A:A").SpecialCells(xlConstants, xlNumbers)
Dim Num As Range
Dim ShtCnt As Long:         ShtCnt = 1

Application.DisplayAlerts = False

For Each Num In RNG
    With wsFrm.Range("A100")
        .Value = Left(.Value, InStrRev(.Value, " ")) & Num.Value
    End With
    ActiveWorkbook.SaveAs Filename:="C:\2010\Run" & ShtCnt & ".txt", FileFormat:=xlText
        'Use FileFormat:=xlTextWindows for Excel 2007
    ShtCnt = ShtCnt + 1
Next Num

End Sub

 

[P.Douglas]

Thank you for your help.

I have changed the reference in 'Set wsFrm =' to where the location of my excel file is that I want to work on

Is this correct? When I go through the step into, I get a 'run-time error '9' Subscript out of range' - I have no idea how to correct this.

Thanks once again

P.Douglas

 

[jbeaucaire]

No, that's not correct. The first thing I said original was "assuming this file is already open", and it sound like it isn't.

So, if we need to open the form first in the macro:

Option Explicit

Sub FillOutForm()
Dim wb As Workbook:         Set wb = Workbooks.Open("C:\2010\Form.xls")
Dim wsFrm As Worksheet:     Set wsFrm = wb.Sheets(1)
Dim wsData As Worksheet:    Set wsData = ThisWorkbook.Sheets(1)
Dim RNG As Range:           Set RNG = wsData.Range("A:A").SpecialCells(xlConstants, xlNumbers)
Dim Num As Range
Dim ShtCnt As Long:         ShtCnt = 1

Application.DisplayAlerts = False

For Each Num In RNG
    With wsFrm.Range("A100")
        .Value = Left(.Value, InStrRev(.Value, " ")) & Num.Value
    End With
    ActiveWorkbook.SaveAs Filename:="C:\2010\Run" & ShtCnt & ".txt", FileFormat:=xlText
        'Use FileFormat:=xlTextWindows for Excel 2007
    ShtCnt = ShtCnt + 1
Next Num

ActiveWorkbook.Close
End Sub