Something wrong with my macro

L

Leighg1809

New Member
Joined
Jan 26, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I wrote my macro and all the headings in columns B,G,L,Q,V,AA,AF,AK,AP,AU,AZ,BE,BU and BO, all cells 11-18 are gone but I haven’t done clear contents in my macro. Please see my macro below
Thanks!!

VBA Code: 
Sub Copy_Sheets()

'

' Copy_Sheets Macro

' Creates Blank Production Schedule, Shift report and ORA sheets

'



'

Sheets(Array("Production Schedule", "Shift Report", "ORA")).Select

Sheets("ORA").Activate

Sheets(Array("Production Schedule", "Shift Report", "ORA")).Copy After:=Sheets(Sheets.Count)

Range("A11:A21").Select

Selection.ClearContents

Range("F11:S21").Select

Selection.ClearContents

Sheets("Shift Report (2)").Select

Range("B4:B10").Select

Selection.ClearContents

Range("D4:D10").Select

Selection.ClearContents

Range("C5:C18").Select

Selection.ClearContents

Range("C19").Select

Selection.ClearContents

Range("C22:D25").Select

Selection.ClearContents

Range("G4:G10").Select

Selection.ClearContents

Range("I4:I10").Select

Selection.ClearContents

Range("H5:H18").Select

Selection.ClearContents

Range("H19").Select

Selection.ClearContents

Range("H22:I25").Select

Selection.ClearContents

Range("L4:L10").Select

Selection.ClearContents

Range("N4:N10").Select

Selection.ClearContents

Range("M5:M18").Select

Selection.ClearContents

Range("M19").Select

Selection.ClearContents

Range("M22:N25").Select

Selection.ClearContents

Range("Q4:Q10").Select

Selection.ClearContents

Range("S4:S10").Select

Selection.ClearContents

Range("R5:R18").Select

Selection.ClearContents

Range("R19").Select

Selection.ClearContents

Range("R22:S25").Select

Selection.ClearContents

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 19

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 16

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 8

ActiveWindow.ScrollColumn = 7

Range("V4:V10").Select

Selection.ClearContents

Range("X4:X10").Select

Selection.ClearContents

Range("W5:W18").Select

Selection.ClearContents

Range("W20").Select

Selection.ClearContents

Range("W22:X25").Select

Selection.ClearContents

Range("AA4:AA10").Select

Selection.ClearContents

Range("AC4:AC10").Select

Selection.ClearContents

Range("AB5:AB18").Select

Selection.ClearContents

Range("AB20").Select

Selection.ClearContents

Range("AB22:AC25").Select

Selection.ClearContents

Range("AD20").Select

ActiveWindow.SmallScroll ToRight:=18

Range("AF4:AF10").Select

Selection.ClearContents

Range("AH4:AH10").Select

Selection.ClearContents

Range("AG5:AG18").Select

Selection.ClearContents

Range("AG20").Select

Selection.ClearContents

Range("AG22:AH25").Select

Selection.ClearContents

Range("AK4:AK10").Select

Selection.ClearContents

Range("AM4:AM10").Select

Selection.ClearContents

Range("AL5:AL18").Select

Selection.ClearContents

Range("AL20").Select

Selection.ClearContents

Range("AL22:AM25").Select

Selection.ClearContents

Range("AP4:AP10").Select

Selection.ClearContents

Range("AR4:AR10").Select

Selection.ClearContents

Range("AQ5:AQ18").Select

Selection.ClearContents

Range("AQ20").Select

Selection.ClearContents

Range("AQ22:AR25").Select

Selection.ClearContents

Range("AU4:AU10").Select

Selection.ClearContents

Range("AW4:AW10").Select

Selection.ClearContents

Range("AV5:AV18").Select

Selection.ClearContents

Range("AV20").Select

Selection.ClearContents

Range("AV22:AW25").Select

Selection.ClearContents

ActiveWindow.SmallScroll ToRight:=19

Range("AZ4:AZ10").Select

Selection.ClearContents

Range("BB4:BB10").Select

Selection.ClearContents

Range("BA5:BA18").Select

Selection.ClearContents

Range("BA20").Select

Selection.ClearContents

Range("BA22:BB25").Select

Selection.ClearContents

Range("BE4:BE10").Select

Selection.ClearContents

Range("BG4:BG10").Select

Selection.ClearContents

Range("BF5:BF18").Select

Selection.ClearContents

Range("BF20").Select

Selection.ClearContents

Range("BF22:BG25").Select

Selection.ClearContents

Range("BJ4:BJ10").Select

Selection.ClearContents

Range("BL4:BL10").Select

Selection.ClearContents

Range("BK5:BK18").Select

Selection.ClearContents

Range("BK20").Select

Selection.ClearContents

Range("BK22:BL25").Select

Selection.ClearContents

ActiveWindow.SmallScroll ToRight:=7

Range("BO4:BO10").Select

Selection.ClearContents

Range("BQ4:BQ10").Select

Selection.ClearContents

Range("BP5:BP18").Select

Selection.ClearContents

Range("BP20").Select

Selection.ClearContents

Range("BP22:BQ25").Select

Selection.ClearContents

ActiveWindow.ScrollColumn = 55

ActiveWindow.ScrollColumn = 54

ActiveWindow.ScrollColumn = 52

ActiveWindow.ScrollColumn = 50

ActiveWindow.ScrollColumn = 49

ActiveWindow.ScrollColumn = 47

ActiveWindow.ScrollColumn = 45

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 20

ActiveWindow.ScrollColumn = 18

ActiveWindow.ScrollColumn = 17

ActiveWindow.ScrollColumn = 15

ActiveWindow.ScrollColumn = 14

ActiveWindow.ScrollColumn = 13

ActiveWindow.ScrollColumn = 12

ActiveWindow.ScrollColumn = 11

ActiveWindow.ScrollColumn = 10

ActiveWindow.ScrollColumn = 9

ActiveWindow.ScrollColumn = 7

ActiveWindow.ScrollColumn = 6

ActiveWindow.ScrollColumn = 4

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Sheets("ORA (2)").Select

Range("B1").Select

Sheets("Production Schedule (2)").Select

Range("A1").Select

End Sub
 
Last edited by a moderator:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
It's not clear what your problem is.
But I propose the following simplification of your code.
Maybe this way you can find what the problem is and update the code.
Or also find some pattern in the cells you want to clean and still make the code simpler.

VBA Code: 
Sub Copy_Sheets()
  Sheets(Array("Production Schedule", "Shift Report", "ORA")).Copy After:=Sheets(Sheets.Count)
  With Sheets("Production Schedule (2)")
    .Range("A11:A21, F11:S21").ClearContents
  End With
  With Sheets("Shift Report (2)")
    .Range("B4:B10,   D4:D10,   C5:C18,   C19,  C22:D25,   G4:G10,  I4:I10,    H5:H18,   H19,  H22:I25").ClearContents
    .Range("L4:L10,   N4:N10,   M5:M18,   M19,  M22:N25,   Q4:Q10,  S4:S10,    R5:R18,   R19,  R22:S25").ClearContents
    .Range("V4:V10,   X4:X10,   W5:W18,   W20,  W22:X25,   AA4:AA10, AC4:AC10, AB5:AB18, AB20, AB22:AC25").ClearContents
    .Range("AF4:AF10, AH4:AH10, AG5:AG18, AG20, AG22:AH25, AK4:AK10, AM4:AM10, AL5:AL18, AL20, AL22:AM25").ClearContents
    .Range("AP4:AP10, AR4:AR10, AQ5:AQ18, AQ20, AQ22:AR25, AU4:AU10, AW4:AW10, AV5:AV18, AV20, AV22:AW25").ClearContents
    .Range("AZ4:AZ10, BB4:BB10, BA5:BA18, BA20, BA22:BB25, BE4:BE10, BG4:BG10, BF5:BF18, BF20, BF22:BG25").ClearContents
    .Range("BJ4:BJ10, BL4:BL10, BK5:BK18, BK20, BK22:BL25, BO4:BO10, BQ4:BQ10, BP5:BP18, BP20, BP22:BQ25").ClearContents
  End With
End Sub
 
Upvote 0
This is the cell pattern you want to clean.

1643393844441.png


So it can be simplified for "Shift Report" sheet:

VBA Code: 
Sub Copy_Sheets_1()
  Dim i As Long, n As Long
  Sheets("Shift Report").Copy After:=Sheets(Sheets.Count)
  For i = 1 To 14
    Sheets("Shift Report (2)").Range("B4:B10, D4:D10, C5:C18, C20, C22:D25").Offset(0, n).ClearContents
    n = n + 5
  Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

VinceF
  • Solved
Clear Contents From 2 Sheets
Replies
8
Views
218
VinceF
VinceF
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
478
kevin9999
K
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
524
ghrek
G
M
  • Question
Loop the Macro to next row until end of sheet
Replies
2
Views
560
rollis13
rollis13
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
335
Joe4
Joe4

Forum statistics

Threads
1,214,613
Messages
6,120,515
Members
448,968
Latest member
Ajax40

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top