Run time error 1004

[Red over White]

<style> <!-- /* Font Definitions */ @font-face {font-family:"Courier New"; panose-1:2 7 3 9 2 2 5 2 4 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:-536859905 -1073711037 9 0 511 0;} @font-face {font-family:Geneva; panose-1:2 11 5 3 3 4 4 4 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:7 0 0 0 147 0;} @font-face {font-family:Wingdings; panose-1:2 0 5 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} @font-face {font-family:"ＭＳ 明朝"; panose-1:0 0 0 0 0 0 0 0 0 0; mso-font-charset:128; mso-generic-font-family:roman; mso-font-formatther; mso-font-pitch:fixed; mso-font-signature:1 134676480 16 0 131072 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} p.MsoListParagraphCxSpFirst, li.MsoListParagraphCxSpFirst, div.MsoListParagraphCxSpFirst {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} p.MsoListParagraphCxSpMiddle, li.MsoListParagraphCxSpMiddle, div.MsoListParagraphCxSpMiddle {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} p.MsoListParagraphCxSpLast, li.MsoListParagraphCxSpLast, div.MsoListParagraphCxSpLast {mso-style-priority:34; mso-style-unhide:no; mso-style-qformat:yes; mso-style-type:export-only; margin-top:0cm; margin-right:0cm; margin-bottom:0cm; margin-left:36.0pt; margin-bottom:.0001pt; mso-add-space:auto; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; mso-fareast-font-family:"ＭＳ 明朝"; mso-fareast-theme-font:minor-fareast; mso-fareast-language:JA;} @page WordSection1 {size:595.0pt 842.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:1514343339; mso-list-type:hybrid; mso-list-template-ids:-1588054054 67698689 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;} @list l0:level1 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Symbol;} @list l0:level2 {mso-level-number-format:bullet; mso-level-text; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:"Courier New"; mso-bidi-font-family:"Times New Roman";} @list l0:level3 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Wingdings;} @list l0:level4 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Symbol;} @list l0:level5 {mso-level-number-format:bullet; mso-level-text; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:"Courier New"; mso-bidi-font-family:"Times New Roman";} @list l0:level6 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Wingdings;} @list l0:level7 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Symbol;} @list l0:level8 {mso-level-number-format:bullet; mso-level-text; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:"Courier New"; mso-bidi-font-family:"Times New Roman";} @list l0:level9 {mso-level-number-format:bullet; mso-level-text:; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt; font-family:Wingdings;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} --> </style> I am fairly new to VBA programming and with people’s help (with 2 macros) have come close to designing what I want the macro to do. My current problem is that whilst the individual macros within ‘Module 2’ will run in their own right when they are displayed above their corresponding page [eg ‘Monthend’ above ‘input’ page], they won’t run when:
· the corresponding page is not above; but more importantly
· I try and run it from the RunMonth macro (which is sourced through a toolbar button).
In both cases I get 1004 runtime messages and it is the ‘Sheets’ pages that are highlighted in yellow.

Any help/suggestions would be appreciated

Module 1

Sub RunMonth()

Call Monthend
Call Input2
Call Input3
Call Testrun
Call Testrun1
Call GraphB

End Sub

Module 2

Sub Monthend()

Sheets("Input").Range("B65536").End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial
Application.CutCopyMode = False

End Sub

Sub Input2()

Dim RowVar As Integer
RowVar = Sheets("Input").Range("A1").Value The entry at InputA1 is a derived formula number
Sheets("Input2").Rows(RowVar - 1 & ":" & RowVar - 1).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Sub Input3()

Dim RowVar As Variant
RowVar = Sheets("Input").Range("A1").Value
Sheets("Input2").Rows(RowVar - 2 & ":" & RowVar - 2).Select
Selection.EntireRow.Hidden = True

End Sub

Sub Testrun()

Sheets("Test Run").Rows("9:9").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Sub Testrun1()

Sheets("Test Run").Rows("8:8").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

End Sub

Sub GraphB() '

Dim i As Integer
Dim ColumnVar As Variant

ColumnVar = Array("B", "C", "D", "E", "F", "G", "J", "K", "L", "M", "N", "O", "P", "U", "V", "W", "X", "Y", "Z", "AA")

For i = 0 To 19
Sheets("Graph B").Select
Cells(Rows.Count, ColumnVar(i)).End(xlUp).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteFormulas
Next i

Sheets("Graph B").Range("AA65536").End(xlUp).Select
Application.CutCopyMode = False

End Sub

 

[Red over White]

Bump

 

[Jerry Sullivan]

Hi,

There are a few things you could do to make this code better.

1. Eliminate use of .Select and .Selection and apply methods like Copy and Paste directly to fully qualified references (ie Worksheets("MyWorksheet").Range("A1")

2. Test to make sure a range exists before trying to apply a method to it. For example, if you apply a filter and there are no rows that meet that criteria, you will get a 1004 error when you try to copy that (non-existent) range of visible cells.

In both cases I get 1004 runtime messages and it is the ‘Sheets’ pages that are highlighted in yellow

You have several Sheets() references, which one is the debugger stopping at?