Code not working as expected

[Wes_A]

Excel 2007, XP Pro.
Once agin I request your much appreciated assistance with an Excel "funny":

The following code appears to run and the rest of the macro runs without any
errors. However the data is NOT pasted into the cells D14 and D8 respectively.
If I step through using F8 then it works, but when the macro is run using
the control button it does not. No error message, just does not paste the
data.

Help!!!

Windows("SCHOOL_MAIN_MENU.xlsm").Activate
Range("K33:L33").Select
Application.CutCopyMode = False
Selection.Copy
Windows("SCHOOL_ID.xlsm").Activate
Sheets("Sheet1").Activate
Range("D14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("SCHOOL_MAIN_MENU.xlsm").Activate
Range("M41").Select
Application.CutCopyMode = False
Selection.Copy
Windows("SCHOOL_ID.xlsm").Activate
Sheets("Sheet1").Activate
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Mike H]

Hi,

I suspect the issue is that your not qualifying the ranges correctly in the
source workbook (SCHOOL_MAIN_MENU.xlsm) by not specifying the sheet to copy
from. Try this ammended and simplified code that assumes sheet1 in the source
workbook.

Windows("SCHOOL_MAIN_MENU.xlsm").Activate
Sheets("Sheet1").Range("K33:L33").Copy
Windows("SCHOOL_ID.xlsm").Activate
Sheets("Sheet1").Range("D14").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("SCHOOL_MAIN_MENU.xlsm").Activate
Sheets("Sheet1").Range("M41").Copy
Windows("SCHOOL_ID.xlsm").Activate
Sheets("Sheet1").Range("D8").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Jacob Skaria]

Try this instead which uses the worksheet object


Sub Macro()
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Workbooks("SCHOOL_MAIN_MENU.xlsm").ActiveSheet
Set ws2 = Workbooks("SCHOOL_ID.xlsm").Sheets("Sheet1")

ws2.Range("D14").Resize(, 2) = Range("K33:L33").Value
ws2.Range("D8") = ws1.Range("M41").Value

End Sub

OR

'using the copy>PasteSpecial
ws1.Range("K33:L33").Copy
ws2.Range("D14").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False