chipsworld
Board Regular
- Joined
- May 23, 2019
- Messages
- 169
- Office Version
- 365
Hi all... Not sure if this is even a thing but...
I am trying to do what I can only describe as a VLOOKUP and a HLOOKUP and copy data to the appropriate cell from one workbook to another.
Basically, I want to open a window, select the import file, and copy the data to the root workbook based off of the column (VLOOKUP) and row (HLOOKUP) and then add the results to the quantity already there. like an x - y coordinate thing...
This is how far I have gotten thus far... I know it isn't very far, and it is returning an error message already, so any help would be great...
The source and destination workbooks/sheets are basically the same....all row titles are in column A and the date is in row 2 across top. this format can be changed to meet whatever I need it to be.
I am trying to do what I can only describe as a VLOOKUP and a HLOOKUP and copy data to the appropriate cell from one workbook to another.
Basically, I want to open a window, select the import file, and copy the data to the root workbook based off of the column (VLOOKUP) and row (HLOOKUP) and then add the results to the quantity already there. like an x - y coordinate thing...
This is how far I have gotten thus far... I know it isn't very far, and it is returning an error message already, so any help would be great...
The source and destination workbooks/sheets are basically the same....all row titles are in column A and the date is in row 2 across top. this format can be changed to meet whatever I need it to be.
VBA Code:
Private Sub cmd_Import_Click()
Dim wbThisWB As Workbook
Dim wbImportWB As Workbook
Dim strFullPath As String
Dim i, m As Long
Set wbThisWB = ThisWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Please select a file to import:"
.Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
.Show
On Error Resume Next 'In case the user has clicked the <Cancel> button
strFullPath = .SelectedItems(1)
If Err.Number <> 0 Then
Exit Sub 'Error has occurred so quit
End If
On Error GoTo 0
End With
Set wbImportWB = Workbooks.Open(strFullPath)
Application.ScreenUpdating = False
' copy data into master sheet
i = wbThisWB.Sheets("s1 daily metrics").Range("a1").Value
m = Application.Match(i, wbImportWB.Sheets("demob ind").Range("A:A"), 0) [B] 'currently getting error message at this point "Run Time Error 9 - Script out of range"[/B]
'If IsError(m) = True Then
'MsgBox "The Value Is Not Found"
'Exit Sub
'End If
wbImportWB.Activate
Sheets("demob ind").Select
Range("B" & m).Select
Selection.Copy
wbThisWB.Activate
Sheets("s1 daily metrics").Select
Range("E1").PasteSpecial xlPasteSpecialOperationAdd
Application.CutCopyMode = False
'wbImportWB.Close False 'Close the Import WB without saving any changes. Uncomment when the code goes live.