0

After reviewing the reference question (is the . in .Range necessary?) I tried removing the dot, adding a Worksheet("sheet1"), deleting the dot in front of the "Cells" object. If I run the code below "ActiveSheet" I clear up the "error 1004" however I never go to "Sheet1" and select the "k --> l" range I'm trying to define. Please advise.

ActiveSheet.Range(Cells(k, 1), Cells(l, 17)).Select

K and L hold the row numbers of the range I'm trying to select to feed into a pivot table. Everything is working until I get to the:

ThisWorkbook.Worksheets ("Sheet1").Range (Cells (k, 1), Cells (l, 17).Select

I get the error1004.

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

            ThisWorkbook.Worksheets("Sheet1").Range(Cells(k, 1), Cells(l, 17)).Select

        End With
Scott
  • 7
  • 7

1 Answers1

1

Assuming k and l are numeric and greater than zero (which means error handling including if your Finds don't retrieve anything) try the following:

With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Cells(k, 1), .Cells(l, 17)).Select
End With

And avoid .Select if it is not needed (which it probably isn't)

So maybe a simple check:

If k > 0 And l > 0 Then
    With ThisWorkbook.Worksheets("Sheet1")
        .Range(.Cells(k, 1), .Cells(l, 17)).Select ' <====get rid of this if possible
    End With
End If

Edit:

For your use

If k > 0 And l > 0 Then
    With ThisWorkbook.Worksheets("Sheet1")
       .Range(.Cells(k, 1), .Cells(l, 17)).Copy Sheets("Sheet2").Range("A1") 
    End With
End If
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Hi Q, I tried without the Select, it does not compile. . in front of Cells either way error 1004. I read the reference question "Is a . in front of Range necessary? " I tried all the recommended edits in that thread, I can get rid of the error 1004 by using "ActiveSheet.Range(Cells(k, 1), Cells(l, 17)).Select" however I'm still not going to "Sheet1" and selecting the "k --> l) row columns I need. – Scott Mar 28 '18 at 16:24
  • Thanks for all the assistance. Q, I found it a problem to use the .Select property. I'm not sure why this works but it does. Any feedback is helpful. ThisWorkbook.Worksheets("Sheet1").Activate ActiveSheet.Range(Cells(k, 1), Cells(l, 17)).Copy Sheets("sheet2").Range("A1") Thanks again for the good effort. Scott – Scott Mar 30 '18 at 15:05
  • .Select selects the object. .Range(.Cells(k, 1), .Cells(l, 17)) by itself will generate an error because nothing is being done with it. It is hanging there..... you could simply have put .Range(.Cells(k, 1), .Cells(l, 17)).Copy Sheets("sheet2").Range("A1") instead of .Range(.Cells(k, 1), .Cells(l, 17)).Select – QHarr Mar 30 '18 at 15:07