Working with a variable Range

[BushyIII]

My MACRO is used daily to copy data from several sheets resulting in a new sheet with a differing number of rows with every run. My problem is that I need to define this range of rows in order to do an AUTOFILL function.

I know I can use a cell to hold the row count using COUNTA. However, I cannot figure out how to then use the result of this in the range statement.
example using COUNTA within cell D100 produces a row count of 95

My MACRO needs to do the following:
A1 =Month(C1)
B1 =Year(C1)
Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A2:B????"),

Thanks in advance

 

[VoG]

Try

Sub fl()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Range("A1").Value = Month(Range("C1").Value)
Range("A1").AutoFill Destination:=Range("A1:A" & LR)
Range("B1").Value = Year(Range("C1").Value)
Range("B1").AutoFill Destination:=Range("B1:B" & LR)
End Sub

 

[mikerickson]

You might use the .End property of a range

Cells(Rows.Count,1).End(xlup)

is the bottom filled row in column 1.

So,

Range(Range("A1"), Cells(Rows.Count,2).End(xlup))

is the range with A1 in the top left corner and the last entry in column 2 (column B) as the lower right cell.

 

[BushyIII]

Please bear with my ignorance.....
Is it necessary to include the Sub fl() and End Sub statements as this code will be repeated for every worksheet in my workbook. There are 9 sheets.

 

[VoG]

Try this - it will operate on all of the sheets:

Sub fl()
Dim LR As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        .Range("A1").Value = Month(.Range("C1").Value)
        .Range("A1").AutoFill Destination:=.Range("A1:A" & LR)
        .Range("B1").Value = Year(.Range("C1").Value)
        .Range("B1").AutoFill Destination:=.Range("B1:B" & LR)
    End With
Next ws
End Sub

 

[BushyIII]

Sorry. I wasn't explicit enough. There are 9 worksheets that require this code but there are several other sheets that do not. Is it possible to explicity name the sheets that this code affects or some other way to exclude those that shouldn't be acted upon?

 

[VoG]

Can you provide a list of either (a) sheets to include or (b) sheets to exclude (whichever is the shorter).

 

[BushyIII]

Exclusion List:
This Month, Monthly Totals, Roll Out

 

[VoG]

Try this

Sub fl()
Dim LR As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name <> "This Month" And .Name <> "Monthly Totals" And .Name <> "Roll Out" Then
            LR = .Range("C" & Rows.Count).End(xlUp).Row
            .Range("A1").Value = Month(.Range("C1").Value)
            .Range("A1").AutoFill Destination:=.Range("A1:A" & LR)
            .Range("B1").Value = Year(.Range("C1").Value)
            .Range("B1").AutoFill Destination:=.Range("B1:B" & LR)
        End If
    End With
Next ws
End Sub

 

[mikerickson]

Dim oneSheet as Worksheet
For each oneSheet in ThisWorkbook.Worksheets
    Select Case oneSheet.Name
        Case Is = "This Month", "Monthly Total", "Roll Out"
        Case Else
            With oneSheet.Range("D:D")
                With Range(.Cells(1,1), .Cells(.Rows.Count,1).End(xlup))
                     .Offset(0,-3).Value = Month(oneSheet.Cells(1,3).Value)
                     .Offset(0,-2).Value = Year(oneSheet.Cells(1,3).Value)
                End With
            End With
        End Select
Next oneSheet