String to Range VBA Help?

[Akira181]

Apologies if my title is incorrect, I'm still learning VBA.

I have two files, one working file with the code, one data source file. I would like to have the working file to pull the relevant rows of data from the source file based on the time entered in the working file.

Currently I am able to select the data source file, enter a start and end time, and find the relevant cells.

I am unable to figure out how to use the found cells to select the rows and copy them across. I keep getting errors and I cannot figure it out. I think it's something to do with the found cells being saved as a string/variant and not as a range. But I cannot get it to work with the variables dim'd as range.

Can anyone tell me what I'm doing wrong?

Private Sub CommandButton1_Click()
Dim FP As Variant
Dim wrk As Workbook
Dim wsCopy As Worksheet
Dim S, E As Variant

'Select Data Source workbook
FP = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
If FP = False Then Exit Sub

Set wrk = Workbooks.Open(Filename:=FP)
Set wsCopy = wrk.Worksheets("sheet1")

'Enter Start and End Time
BeginTime = InputBox("Please enter your start time.")
EndTime = InputBox("Please enter your end time.")

'Search source data for start time
Set BeginRNG = wsCopy.Cells.Find(What:=BeginTime, _
                                        LookIn:=xlValues, _
                                        Lookat:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
    S = BeginRNG.Address
  
'Search source data for end time
Set EndRNG = wsCopy.Cells.Find(What:=EndTime, _
                                        LookIn:=xlValues, _
                                        Lookat:=xlPart, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
    E = EndRNG.Address

'Error check
If BeginRNG Is Nothing Then
    MsgBox "No match found." & vbNewLine & "Please enter a valid starting time."
ElseIf EndRNG Is Nothing Then
    MsgBox "No match found." & vbNewLine & "Please enter a valid ending time."
Else

'Copy selected range to active workbook
wsCopy.Range(Cells(S, 1), Cells(E, 1)).EntireRow.Copy
    ActiveWorkbook.ActiveSheet.Range ("A5")

End If

' Close the source file.
    wrk.Close False         ' False, so you don't save the source file.
    Set wrk = Nothing

End Sub

 

[MARK858]

The obvious things that I can see are wrong are

wsCopy.Range(Cells(S, 1), Cells(E, 1)).EntireRow.Copy
    ActiveWorkbook.ActiveSheet.Range ("A5")

try changing it to

wsCopy.Range(wsCopy.Cells(S, 1), wsCopy.Cells(E, 1)).EntireRow.Copy Range("A5")

and as you are using Cells

S = BeginRNG.Address

should be

 S = BeginRNG.Row

and

E = EndRNG.Address

should be

E = EndRNG.Row

If you still have errors after that then please post on what line the error occurs and what the error states.

 

[Akira181]

Thanks very much! Sorry for the late reply, was so happy it worked I continued working on the next bit.

I figured out the .row bit but don't think I would ever have gotten the wsCopy.Range(wsCopy.Cells...) bit. I thought the first wsCopy would have been enough to point it to the correct workbook. Thanks again!

 

[MARK858]

I wasn't just the worksheet (you could remove the first wsCopy btw), you also had the Copy/"Paste" on 2 lines. To do that you would have needed to use PasteSpecial.
Rather than that I put it on the one line so it could use Destination rather than PasteSpecial.

Happy you have it sorted