Why is my Select Case applying to more than what I am selecting?

[statiCat]

On every other page of my workbook I have data in cells A4:O459 and I would like to extend the last 8 rows with their formulas and cut/paste all rows excluding the top 8 over all the rows. The result being that I have 8 fresh rows at the bottom and the oldest 8 at the top are gone.

On every other page I have data in cells B3:I59 and I would like to do the same thing but only create/get rid of 1 row.

The following code seems to be cutting and pasting A4:O459 on every page, despite the Case statement. I end up with an error saying that it can't paste into an area smaller than the selection. Why is this happening?

Sub getNewRows()
    Dim ws As Worksheet
    For Each ws In Worksheets
        Select Case ws.Name
            Case "sheet1", "sheet3"
                    Range("A4:O459").Select  'select current data
                    Selection.AutoFill Destination:=Range("A4:O467"), Type:=xlFillSeries 'extend 8 lines
                    Range("A12:O467").Select   'grab all except top 8
                    Selection.Cut
                    Range("A4").Select   'paste where original were
                    ws.Paste

            Case "sheet2", "sheet4"
                    Range("B3:I59").Select 'select current data
                    Selection.AutoFill Destination:=Range("B3:I60"), Type:=xlFillSeries    'extend 1 line
                    Range("B3:I60").Select
                    Range("B4:I60").Select   'grab all except top 1
                    Selection.Cut
                    Range("B3").Select   'paste where original were
                    ws.Paste
        End Select
        
        Next ws
    
End Sub

 

[Jon Peltier]

Use code tags to help make your code readable. Select it and click on the '#' icon while editing, or put [ c o d e ] (without spaces) before and [ / c o d e ] after the code.

If you use Range, VBA thinks you mean the active sheet. Either activate the sheet, or better, reference Range with the worksheet variable, as ws.Range. You can't select a range that's not on the active sheet, so use Application.GoTo. I have not made any other effort to clean up the code; eventually you'll want to get rid of all the Range.Select statements.

Sub getNewRows()
  Dim ws As Worksheet

  For Each ws In Worksheets
    Select Case ws.Name
      Case "sheet1", "sheet3"
        Application.GoTo ws.Range("A4:O459")
        Selection.AutoFill Destination:=Range("A4:O467"), Type:=xlFillSeries 'extend 8 lines
        ws.Range("A12:O467").Select 'grab all except top 8
        Selection.Cut
        ws.Range("A4").Select 'paste where original were
        ws.Paste

      Case "sheet2", "sheet4"
        Application.GoTo ws.Range("B3:I59")
        Selection.AutoFill Destination:=Range("B3:I60"), Type:=xlFillSeries 'extend 1 line
        ws.Range("B3:I60").Select
        ws.Range("B4:I60").Select 'grab all except top 1
        Selection.Cut
        ws.Range("B3").Select 'paste where original were
        ws.Paste

    End Select

  Next ws

End Sub

 

[statiCat]

Thanks, that solves the selection problem! However, I'm noticing that the formulas that are supposed to drag down are not dragging down properly. So in the worksheets that have 8 lines to drag down the dates go 4/6, 4/7, 4/8, 4/9, 4/10, 4/11, 4/12, 4/6 (the last lines summarizes the week) and I want it to continue the same pattern for the next week starting with 4/13, but for some reason Excel is jumping down to 3/17. I believe the line of code that handles this is:

 Selection.AutoFill Destination:=Range("A4:O467"), Type:=xlFillSeries

Is there a way to ensure it follows the right pattern?

 

[Jon Peltier]

I think you're asking a lot to expect Excel to guess what that pattern is. Could you instead put a formula into those cells that refers to the cell 8 rows higher and adds 7?

Assuming the dates are in column A, cell A460 would say

=A452+7

 

[statiCat]

Thanks, I will try that!