I've got the following macro:
Sub SearchString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim myPath As String
Dim myFileName As String
Dim wkbkSourceWasOpen As Boolean
Dim wkbkSource As Workbook
myPath = "C:\"
myFileName = "debit.xls"
Set wkbkSource = Nothing
On Error Resume Next
Set wkbkSource = Workbooks(myFileName)
On Error GoTo 0
wkbkSourceWasOpen = True
If wkbkSource Is Nothing Then
wkbkSourceWasOpen = False
On Error Resume Next
Set wkbkSource = Workbooks.Open(Filename:=myPath & myFileName, ReadOnly:=True)
On Error GoTo 0
If wkbkSource Is Nothing Then
MsgBox "Run for your life"
End If
End If
wkbkSource.Sheets("ddd").Select
'Start search in row 71
LSearchRow = 1
'Start copying data to row 5 in urea (row counter variable)
LCopyToRow = 1
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column E = "---", copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = "ST" Then
'Select row in Sheet1 to copy
wkbkSource.Sheets("ddd").Select
Range("C" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet2 in next row
Sheets("Ëèñò2").Select
Range("B" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet1 to continue searching
Sheets("ddd").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A1").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
and I need to copy data from a closed book to an active workbook sheet (where macro is written). It does copy data, but to a sheet in the same closed workbook, which it opens up. Could somebody give advice as to how change it to "ThisWorkbook.Sheet paste" function?
Sub SearchString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim myPath As String
Dim myFileName As String
Dim wkbkSourceWasOpen As Boolean
Dim wkbkSource As Workbook
myPath = "C:\"
myFileName = "debit.xls"
Set wkbkSource = Nothing
On Error Resume Next
Set wkbkSource = Workbooks(myFileName)
On Error GoTo 0
wkbkSourceWasOpen = True
If wkbkSource Is Nothing Then
wkbkSourceWasOpen = False
On Error Resume Next
Set wkbkSource = Workbooks.Open(Filename:=myPath & myFileName, ReadOnly:=True)
On Error GoTo 0
If wkbkSource Is Nothing Then
MsgBox "Run for your life"
End If
End If
wkbkSource.Sheets("ddd").Select
'Start search in row 71
LSearchRow = 1
'Start copying data to row 5 in urea (row counter variable)
LCopyToRow = 1
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column E = "---", copy entire row to Sheet2
If Range("A" & CStr(LSearchRow)).Value = "ST" Then
'Select row in Sheet1 to copy
wkbkSource.Sheets("ddd").Select
Range("C" & CStr(LSearchRow)).Select
Selection.Copy
'Paste row into Sheet2 in next row
Sheets("Ëèñò2").Select
Range("B" & CStr(LCopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
LCopyToRow = LCopyToRow + 1
'Go back to Sheet1 to continue searching
Sheets("ddd").Select
End If
LSearchRow = LSearchRow + 1
Wend
'Position on cell A3
Application.CutCopyMode = False
Range("A1").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
and I need to copy data from a closed book to an active workbook sheet (where macro is written). It does copy data, but to a sheet in the same closed workbook, which it opens up. Could somebody give advice as to how change it to "ThisWorkbook.Sheet paste" function?
