Hi All
I am totally stumped.
I am using the following code
Code
Dim OpFile As String
OpFile = SvPath & "Man Ac - " & Format(Range("Month").Value, "mmm yy") & ".xlsm"
Dim File As String
File = "Man Ac - " & Format(Range("Month").Value, "mmm yy") & ".xlsm"
Workbooks.Open OpFile 'Open Source File
Dim rng As Range
Set rng = ActiveWorkbook.Sheets("Selection").Range("N2:N11")
Dim FRng As Range
Set FRng = ActiveWorkbook.Sheets("Selection").Range("AA2:AE11")
Workbooks(File).Activate
With ActiveWorkbook
For Each cell In rng
Sheets(cell.Value).Activate
Range("B7").Activate
Do
If ActiveCell <> Range("Month").Value Then
ActiveCell.Offset(0, 1).Activate
End If
Loop Until ActiveCell.Value = Range("Month").Value
ActiveCell.Offset(19, 0).Copy
cell.Offset(0, 3).PasteSpecial xlPasteValues 'Revenue Actual
ActiveCell.Offset(1, 0).Activate
ActiveCell.Offset(18, 1).Copy
cell.Offset(0, 8).PasteSpecial xlPasteValues 'Revenue Budget
ActiveCell.Offset(16, 0).Copy
cell.Offset(0, 4).PasteSpecial xlPasteValues 'Interest Rec'd Actual
ActiveCell.Offset(16, 1).Copy
cell.Offset(0, 9).PasteSpecial xlPasteValues 'Interest Rec'd Budget
ActiveCell.Offset(27, 0).Copy
cell.Offset(0, 5).PasteSpecial xlPasteValues 'Direct Actual
ActiveCell.Offset(27, 1).Copy
cell.Offset(0, 10).PasteSpecial xlPasteValues 'Direct Budget
'*********
ActiveCell.Offset(54, 0).Copy
cell.Offset(0, 6).PasteSpecial xlPasteValues 'InDirect Actual
ActiveCell.Offset(54, 1).Copy
cell.Offset(0, 11).PasteSpecial xlPasteValues 'InDirect Budget
Next cell
Exit Sub
End With
Display More
which when i run direct from the module - i get the correct data -
However when i run from a Button it seem to start fail from (marked with an astrix)
It looks like the code starts getting the info from the sheet I past too, rather than the sheet I copy from.
Sadly the workbook is far too large to attach (the Source workbook is 820kb) and the workbook with the code is 195kb
There is other code after the end with, which all works correctly