Inability to Copy Sheet to Other Workbooks

[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

 

[Guest]

Sub Insert_Instructions()

Dim wbSource As Workbook
Dim wsInstructions As Worksheet

Dim vPath as string
Dim vName as Striing


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(vName).Sheets(1)
Workbooks(vName).Close SaveChanges:=True
End If

vName = Dir
Loop

wbSource.Close SaveChanges:=False

Set wsInstructions = Nothing
Set wbSource = Nothing

End Sub

 

[Henry Stockbridge]

Sub Insert_Instructions()

Dim wbSource As Workbook
Dim wsInstructions As Worksheet

Dim vPath as string
Dim vName as Striing

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(vName).Sheets(1)
Workbooks(vName).Close SaveChanges:=True
End If

vName = Dir
Loop

wbSource.Close SaveChanges:=False

Set wsInstructions = Nothing
Set wbSource = Nothing

End Sub

--
Regards,
Tom Ogilvy














- Show quoted text -

===========================
Tom,

Thanks for the help. Your code works like a charm.

Henry

=======================================