Hi
I am very new to macro i have created a bar stock control sheet to monitor variances on a daily basis.My workbook consists of 5 sheets.Sheet 1 is "To Print" only for printing purpose to start count, sheet 2 is "Variance", sheet 3 "Sales History", sheet 4 "Stock Count", sheet 5 "DAY". I have created a button on sheet 4 called next day which is the following:
Sub NextDay()
'
' NextDay Macro
' GDavelis
'
'
Sheet6.Unprotect Password:="1"
Sheets("DAY").Select
Range("E2:E280").Select
Selection.Copy
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C2:D280").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Stock Count").Select
Range("B3:C281").Select
Selection.ClearContents
Range("M6").Select
Sheet6.Protect Password:="1"
End Sub
MY Problem is to add formula before buttons clears totals i need to copy in sheet 5 "DAY" H2:H280 and paste special into sheet 2 "Variances" Starting from B2:B280 as well as copy from sheet 5 "DAY" D2:D280 and paste special into sheet 3 "Sales History" starting from B2:B280, but every new day needs to paste special into next column C2:C280 for both pastes and so on ....
Bookmarks