I have a master excel file, Workbook A, that I do all the calculations and formulas on but every month I will download a file called Workbook B that has a changing workbook and sheet name, but the format stays the same.
The steps that I am looking to achieve is to clear all previous entries from Workbook A and copy a range of cells from Workbook B sheet 1, column B, C, D, row 5 to lastrow, onto Workbook A sheet 2, column A row 3.
I tried googling for different codes to use and tried modifying to my needs but so far I am unable to make my own modification work due to limited knowledge on VBA.
Sub DB2Lookup()
Dim wb As Workbook
Dim sht As Worksheet
Set wb = ActiveWorkbook
Set sht2 = wb.Sheets(2)
lastrow = Range("A" & Rows.Count).End(xlUp).Row
sht2.Range("A3:C3" & lastrow).Clear
Dim FileName As String
FileName = ""
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select File"
.Filters.Add "Excel File", "*.xls?"
.AllowMultiSelect = False
If .Show Then
FileName = .SelectedItems(1)
End If
End With
Dim tempworkbook As Workbook
Set tempworkbook = Workbooks.Open(FileName, ReadOnly:=True)
tempworkbook.Range("B5:B" & lastrow).Value = wb.Sheets(1).Cells(1, 3).Value
End Sub
I also wish to understand the logic behind the code itself to learn more. From what I understand so far is that after clearing the values, VBA will prompt the user to select a file due to FileDialog function, and then if the dialog shows then the selected file will be FileName in a string format. Then tempworkbook will then be tagged to the file that was selected, and then copy the ranges from the tempworkbook onto Workbook A. The error I gotten was "Object doesn't support this property or method" which I do not know how to modify it further.
Do let me know if my understanding of the code is wrong.
