Search for value on a closed workbook and copy to an open workbook

[gasper21]

Question How to copy from one workbook to another? I’m trying to search for text "Total LOE" that is on a closed workbook and copy the row to an open workbook. This macro works however I have to open the workbook that contains the "Total LOE" and copy it manually.

I sincerely appreciate your advice.

sub CopyLOE()
Dim ws As Worksheet
Dim DataFile As String

Set ws = ActiveSheet
MsgBox ("Please select a file to copy data from.")
DataFile = Application.GetOpenFilename("Excel Files(*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", 1, "Select One File To Open", , False)
If DataFile <> "False" Then
Workbooks.Open DataFile, UpdateLinks:=False
Range("A41:N41").Copy
ws.Range("A85").PasteSpecial xlPasteValues
ActiveWorkbook.Close False
End If
End Sub

 

[KennyGreens]

Hi there, I'm not sure how the "Total LOE" really plays in this, so if you can explain that a bit more I could probably help a bit more. However, currently you aren't really declaring which workbook it should be copying from, so that might be part of the reason why it's not copying anything. Try something like this:

Sub CopyLOE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim currWB As Workbook
Dim newWB As Workbook
Dim ws As Worksheet
Dim DataFile As String

Set currWB = ThisWorkbook
Set ws = currWB.Sheets(ActiveSheet.Index)

MsgBox ("Please select a file to copy data from.")
DataFile = Application.GetOpenFilename("Excel Files(*.xls; *.xlsx; *.xlsm), *.xls; *.xlsx; *.xlsm", 1, "Select One File To Open", , False)

If DataFile <> "False" Then
    Workbooks.Open DataFile, UpdateLinks:=False
    Set newWB = ActiveWorkbook
    newWB.ActiveSheet.Range("A41:N41").Copy
    currWB.ws.Range("A85").PasteSpecial xlPasteValues
    newWB.Close False
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[gasper21]

Hi Kenny, thank you for responding. I'm sorry I didn't explain myself clearly.

The "Total LOE" is the description that I'm searching for on the workbook that is closed, the Total LOE is not in the same row every time.
I would like to have a macro that will find so Total LOE and then copy the entire row to the wordbook that I have open on row A81

Description
[TABLE="width: 216"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Roustabout Services[/TD]
[/TR]
[TR]
[TD]Environmental CleanUp[/TD]
[/TR]
[TR]
[TD]1 Regulatory[/TD]
[/TR]
[TR]
[TD]2 Miscellaneous[/TD]
[/TR]
[TR]
[TD]3 P & A[/TD]
[/TR]
[TR]
[TD]4 Ad Valorem Tax[/TD]
[/TR]
[TR]
[TD]6 TOTAL LOE:[/TD]
[/TR]
</tbody>[/TABLE]

 

[KennyGreens]

Hi Kenny, thank you for responding. I'm sorry I didn't explain myself clearly.

The "Total LOE" is the description that I'm searching for on the workbook that is closed, the Total LOE is not in the same row every time.
I would like to have a macro that will find so Total LOE and then copy the entire row to the wordbook that I have open on row A81

Description
[TABLE="width: 216"]
<tbody>[TR]
[TD]Roustabout Services
[/TD]
[/TR]
[TR]
[TD]Environmental CleanUp
[/TD]
[/TR]
[TR]
[TD]1 Regulatory
[/TD]
[/TR]
[TR]
[TD]2 Miscellaneous
[/TD]
[/TR]
[TR]
[TD]3 P & A
[/TD]
[/TR]
[TR]
[TD]4 Ad Valorem Tax
[/TD]
[/TR]
[TR]
[TD]6 TOTAL LOE:
[/TD]
[/TR]
</tbody>[/TABLE]

That's no problem. So do you mean that the "Total LOE" is in column F and you want to copy each instance of it? For example if row 40 in the closed workbook in column F has "Total LOE" in it, copy it to row 81 in the open workbook, then find the next instance for example in row 50 and copy row 50 to row 82 in the open workbook?