I don't see the problem? Bob Philips originally helped with this project and everything worked fine, now the macro will not paste data from one workbook to another.
The code, yea its ugly, just look at it with one eye and it aint so bad.
Sub OpenWorkbooks_Instructions()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim vNewSel As Variant
Dim sFileName As String
'Copy selection set to 50 rows maximum, change as needed
'Modified macro from Bob Phillips
Application.ScreenUpdating = False
Sheets("INSTRUCTIONS").Select 'select cells from "INSTRUCTIONS" workbook
Cells.Select
Selection.Rows.AutoFit 'autofit rows
Range("A1:I102").Select 'copy all necessary cells (50 row setting)
Selection.Copy
vNewSel = Selection.Copy 'set selection into variable
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("M:\Qadocs\IPI'S\Test Folder\Run") 'folder location 'test folder location, "C:\MyTest"
For Each file In Folder.Files 'iterate thru all files in folder that have "xls" suffix
sFileName = file
If Right(sFileName, 3) = "xls" Then 'check for "xls" suffix
Workbooks.Open FileName:=file.Path, Password:="2000", WriteResPassword:="2000" 'pass workbook.open password
Worksheets("INSTRUCTIONS").Activate 'select worksheet "INSTRUCTIONS"
'select range of cells to paste over
Range("A1:I102").PasteSpecial
ActiveSheet.Paste 'paste cells from original worksheet
Cells.Select
Selection.Rows.AutoFit 'auto fit rows
Range("A1").Select 'clear main selection
Worksheets("Master Sheet").Select 'change to first sheet in workbook
ActiveWorkbook.Close SaveChanges:=True 'save changes
End If
Next file
Set oFSO = Nothing
Application.ScreenUpdating = True
End Sub
This bit "'ActiveSheet.Paste 'paste cells from original worksheet" is the code to paste which fails
Error: Run-time error 1004
Paste method of Worksheet class failed
I am just copying cells A1 thru I102 and paste to another workbook in the same range. This macro worked fine until, dare I say it, the dreaded SP3. <sends shivers up my spine>
As always, any help is appreciated.
(Spell checker isnt working so you might want to close both eyes)
Bookmarks