I have two Excel Macro/VB question.

The first sounds rather simple. There is already a VB script written
that prompts the user to open a text file and subsequently gathers
information from that file. What i'd like to do is automate the
process such that the script goes into a folder and pulls all of the
text files one by one while gathering the information (ie. no
subsequent prompts).

Here's what the code looks like now (note this was written by someone
very familiar with VB who no longer works with us).

*********************************************************************
Sub Read_Specimen_data()

' This subroutine will read in the specimen Label, Width, Thickness,
and Max Load
' from a file and place them into columns of the worksheet.

Dim line1 As String * 80
Dim label As String
Dim filename As String
Dim layupcode As String
Dim comment As String
Dim testdate As String

Dim nrow As Integer
Dim nspec As Integer

Dim width As Double
Dim thickness As Double
Dim maxload As Double

Dim ws1 As Worksheet

Set ws1 = Worksheets("Data")

' Turn off screen updating to speed up the process

Application.ScreenUpdating = False

nrow = Application.WorksheetFunction.CountA(ws1.Range("C:C")) + 1

' Get the file name from the user

filename = Application.GetOpenFilename
Open filename For Input As #1

' Read a line from the input file

Do While Not EOF(1) ' Check for end of file

Input #1, line1

If (Mid(line1, 1, 9) = "Sample ID") Then

testdate = Mid(line1, 49, 13)

End If


If (Mid(line1, 1, 19) = "Number of specimens") Then

nspec = Mid(line1, 21, 6)

End If

If (Mid(line1, 1, 6) = "1:[LAY") Then

layupcode = Mid(line1, 26, 40)

End If

If (Mid(line1, 1, 6) = "2:[COM") Then

comment = Mid(line1, 26, 40)

End If

If (Mid(line1, 1, 5) = "Width") Then

width = Mid(line1, 19, 6)
ws1.Cells(nrow, 4).Value = width

End If

If (Mid(line1, 1, 9) = "Thickness") Then

thickness = Mid(line1, 19, 6)
ws1.Cells(nrow, 5).Value = thickness

End If

If (Mid(line1, 1, 14) = "Specimen label") Then

label = Mid(line1, 18, 10)
ws1.Cells(nrow, 3).Value = label

End If

If (Mid(line1, 1, 18) = "Maximum Load point") Then

maxload = Mid(line1, 53, 10)
ws1.Cells(nrow, 6).Value = maxload
nrow = nrow + 1

End If

Loop

Close #1

ws1.Cells(nrow - nspec, 1).Value = filename
ws1.Cells(nrow - nspec, 2).Value = testdate
ws1.Cells(nrow - nspec, 7).Value = layupcode
ws1.Cells(nrow - nspec, 8).Value = comment

Range(Cells(nrow - nspec, 1), Cells(nrow - 1, 1)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 2), Cells(nrow - 1, 2)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 7), Cells(nrow - 1, 7)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 8), Cells(nrow - 1, 8)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

End Sub
*********************************************************************


The second issue is more complex.

I have created a macro that I'd like to repeat in multiple cells, but
the trick is that the range selected by the macro changes everytime,
and it also changes worksheets.

So in lamens terms, here's what the macro does:

copy info from cells. (in parent worksheet)
paste into cells below.
select cell (in which information is pasted) and highlight range.
Move to next worksheet and select a range.
and update information on the parent (data) worksheet.

What I'd like it to do is to repeat this action (copy and paste into
other cells) while selecting a different range in a different
worksheet.

The macro looks like this:

*******************************************************************
Range("S2:U2").Select
Selection.Copy
Range("S5").Select
ActiveSheet.Paste
Range("S5").Select
Sheets("017049RA04").Select
Range("D99").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-R99C2"
Range("E99").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("F99").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("D99:F99").Select
Selection.Copy
Range("D100:F452").Select
ActiveSheet.Paste
Range("F103").Select
Application.CutCopyMode = False
Sheets("Data").Select
Range("S5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(0.004,Data!R[94]C[-15]:R[447]C
[-13],2,TRUE)"
Range("S5").Select
Sheets("017049RA04").Select
Sheets("017049RA04").Name = "017049RA04"
Sheets("Data").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(0.004,'017049RA04'!R[94]C[-15]:R[447]C[-13],2,TRUE)"
Range("S5").Select
Selection.Copy
Range("T5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'017049RA04'!R[94]C[-15]:R[447]C[-
13],2,TRUE)"
Range("T5").Select
Selection.NumberFormat = "#,##0.0"
Selection.NumberFormat = "#,##0.00"
Selection.NumberFormat = "#,##0.000"
Selection.NumberFormat = "#,##0.0000"
Selection.NumberFormat = "#,##0.00000"
Selection.NumberFormat = "#,##0.000000"

*******************************************************************


Let me know if this makes sense and if you can point me to good
VB/Macro online tutorials.