Am I Doing Something Stupid?

[dominicwellsuk]

I have taken a piece of code from the web to run a macro on all files in a given folder, and added some very simple code into the middle of it. Full code is as follows:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Range("A1:E1839").Select
Selection.Copy
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "\\uk-ntce-s0100.NissanEU.NMCorp.nissan.biz\n_nepe\nepe\03 Process Shops 24G\350 T+C Shop\P32S\21. Design Standard Time\DST Comparison Files\02. Completed\DW"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE


Sheets("Lookup").Select
ActiveSheet.Unprotect
Range("A1:E1839").Select
ActiveSheet.paste
ActiveSheet.Protect

wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub


You'll see that at the start of the macro, I copy a range of data onto the clipboard, and the repeated action for all files in the given folder is to paste this onto cell A1 of the sheet "Lookup".

For some reason, the information is not posted, but the macro goes through the motions as if it wants to do it. Have I done something obviously wrong which means these cells won't be pasted?

Thanks,

Dom

 

[AlphaFrog]

Try something like this...

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
[COLOR="Red"]Dim rngCopy As Range[/COLOR]

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

[COLOR="Red"]Set rngCopy = Range("A1:E1839")[/COLOR] [COLOR="Green"]'Note: rngCopy remembers its Workbook and Worksheet[/COLOR]
[COLOR="Magenta"]On Error Resume Next[/COLOR]
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "\\uk-ntce-s0100.NissanEU.NMCorp.nissan.biz\n_nepe\nepe\03 Process Shops 24G\350 T+C Shop\P32S\21. Design Standard Time\DST Comparison Files\02. Completed\DW"
.FileType = msoFileTypeExcelWorkbooks
'Optional filter with wildcard
'.Filename = "Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE


Sheets("Lookup").Select
ActiveSheet.Unprotect
[COLOR="Red"]rngCopy.Copy Destination:=ActiveSheet.Range("A1:E1839")[/COLOR]
ActiveSheet.Protect

wbResults.Close SaveChanges:=True
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Also: On Error resume Next will hide any errors your code may have.