Hi all,
I'm trying to select a range of cells in a data sheet defined by integers k and l. I need to select that range before I input it into a pivot table (code not shown here)
When I use the .Select property I get a Error 1004. Any advise would be welcome here.
ActiveSheet.Range(.Cells(k, 1), .Cells(l, 17)).Select
Thanks in advance,
Scott
' Code to select custom data range k to l.
Public Sub Create_Report()
'Define range of report
ww_from = Application.InputBox _
(prompt:="From what is the workweek do you want to report?", Type:=1)
ww_to = Application.InputBox _
(prompt:="To what workweek do you want to report?", Type:=1)
'Find first row of data set locations
With ThisWorkbook.Worksheets("Sheet1").Range("C:C")
Set First = .Find(ww_from)
'Locate data set for report by row number
If Not First Is Nothing Then k = First.Row
'Find last row of data set locations
Set Last = .Find(ww_to + 1)
'Locate data set for report by row number
If Not Last Is Nothing Then
l = Last.Row - 1
Else
l = k
End If
ActiveSheet.Range(.Cells(k, 1), .Cells(l, 17)).Select
End With
End Sub
I'm trying to select a range of cells in a data sheet defined by integers k and l. I need to select that range before I input it into a pivot table (code not shown here)
When I use the .Select property I get a Error 1004. Any advise would be welcome here.
ActiveSheet.Range(.Cells(k, 1), .Cells(l, 17)).Select
Thanks in advance,
Scott
' Code to select custom data range k to l.
Public Sub Create_Report()
'Define range of report
ww_from = Application.InputBox _
(prompt:="From what is the workweek do you want to report?", Type:=1)
ww_to = Application.InputBox _
(prompt:="To what workweek do you want to report?", Type:=1)
'Find first row of data set locations
With ThisWorkbook.Worksheets("Sheet1").Range("C:C")
Set First = .Find(ww_from)
'Locate data set for report by row number
If Not First Is Nothing Then k = First.Row
'Find last row of data set locations
Set Last = .Find(ww_to + 1)
'Locate data set for report by row number
If Not Last Is Nothing Then
l = Last.Row - 1
Else
l = k
End If
ActiveSheet.Range(.Cells(k, 1), .Cells(l, 17)).Select
End With
End Sub