Excel VBA
I am trying to find out if it is possible to create a macro in an excel workbook that will open another workbook saved on the computer, perform a find, copy the data, and paste it in the opened/active workbook.
I have been banging my head on the wall for several days trying to figure this out, and I am no closer. I am just hoping someone could give me some kind of guidance, or let me know if I am wasting my time.
# 4 Re: Excel VBA
Here is what I have in my code now:
Dim basebook As Workbook
Dim mybook As Workbook
Dim mybook2 As Workbook
Dim sourceRange As Range
Dim sourceRange2 As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim rnum2 As Long
Dim rnum3 As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FileName1 As Variant
Dim FileName2 As Variant
Dim FileName3 As Variant
SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\cgravlee\Desktop\Test"
ChDrive MyPath
ChDir MyPath
FileName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.csv), *.csv", _
MultiSelect:=True)
If IsArray(FileName1) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 16 'This tells what row
For N = LBound(FileName1) To UBound(FileName1)
Set mybook = Workbooks.Open(FileName1(N))
Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="Customer ID", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")
rnum = 5 'This tells what row
basebook.Worksheets(1).Cells(rnum, "A").Value = "Sample ID: " + mybook.Name
sourceRange.Copy destrange
mybook.Close False
rnum = rnum + SourceRcount
Next
End If
If IsArray(FileName1) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 17 'This tells what row
For N = LBound(FileName1) To UBound(FileName1)
Set mybook = Workbooks.Open(FileName1(N))
Set sourceRange = mybook.Worksheets(1).Cells.Find(What:="Product ID", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")
sourceRange.Copy destrange
mybook.Close False
rnum = rnum + SourceRcount
Next
End If
I believe I have the find, copy, and paste working, unless someone can simplify what I have. However, what I would like to do now is determine a way to not only copy the data spefified in the find, bu also copy some other data that is inthe same row without having to specify a cell or range.
Can this be done? If so, how?
