H
Henry Stockbridge
Hi,
I need to copy an instruction sheet from one workbook into a series of
workbooks. When I run the following procedure, I either get a
'Subscript out of Range' , or the procedure runs, but does not copy
the sheet. Any help you can lend with this would be most helpful.
--------------------------------------------
Sub Insert_Instructions()
Dim wbSource As Workbook
Dim wsInstructions As Worksheet
Dim vPath ' Unsure - string or variant?
Dim vName ' Unsure - string or variant?
Set wbSource = Workbooks.Open("c:\Instructions.xls")
Set wsInstructions = wbSource.Worksheets("Instructions")
vPath = "c:\ChangeRequests\"
vName = Dir(vPath, vbNormal)
Do While Not vName <> ""
If (GetAttr(vPath & vName) And vbNormal) = vbNormal Then
Workbooks.Open vPath & vName
wsInstructions.Copy Before:=Workbooks(vPath &
vName).Sheets(1)
Workbooks(vPath & vName).Save
Workbooks(vPath & vName).Close
End If
vName = Dir
Loop
wbSource.Close SaveChanges:=False
Set wsInstructions = Nothing
Set wbSource = Nothing
End Sub
I need to copy an instruction sheet from one workbook into a series of
workbooks. When I run the following procedure, I either get a
'Subscript out of Range' , or the procedure runs, but does not copy
the sheet. Any help you can lend with this would be most helpful.
--------------------------------------------
Sub Insert_Instructions()
Dim wbSource As Workbook
Dim wsInstructions As Worksheet
Dim vPath ' Unsure - string or variant?
Dim vName ' Unsure - string or variant?
Set wbSource = Workbooks.Open("c:\Instructions.xls")
Set wsInstructions = wbSource.Worksheets("Instructions")
vPath = "c:\ChangeRequests\"
vName = Dir(vPath, vbNormal)
Do While Not vName <> ""
If (GetAttr(vPath & vName) And vbNormal) = vbNormal Then
Workbooks.Open vPath & vName
wsInstructions.Copy Before:=Workbooks(vPath &
vName).Sheets(1)
Workbooks(vPath & vName).Save
Workbooks(vPath & vName).Close
End If
vName = Dir
Loop
wbSource.Close SaveChanges:=False
Set wsInstructions = Nothing
Set wbSource = Nothing
End Sub