Automating report with macro

[Byandell]

I am attempting to create a Macro to auto-update an existing report.

Using Microsoft Office Home & Business 2016 Edition - version 1711

Scenario:
Have existing workbook named Expense Report, with 6 worksheets all named by different days and a total page.

I also have a web based CSR program that I download (export) weekly meeting activity with notes in .xls format.

The "Expense Report" has "merged cells" and protected areas, as well as limited space to type.

I understand I have to download the CSR data and save it to my pc in order to create a macro for the two books to communicate, however, I need assistance in creating the macro to copy specific cell information from the CSR report to the Expense Report. I know it can be done, just don't know where to start!

 

[PatOBrien198]

Have you tried recording a macro while you do it manually one time?

Then see what variables you may need to change to make it flexible.

 

[Virici]

It can definately be automated, as long as the values are located in static cells in the downloaded export file (does not change over time).

Have a look at something like this code to inspire you. You need to apply to a module in your Expense Report file.
Alternatively provide example files to assist with the task, if you're not too familiar with VBA.

Sub Update()

Dim wb As Workbook
Dim filepath As String

'Enter directory path and filename of the export file - this can also exist as a value in your workbook
filepath = *INSERT PATH AND FILENAME HERE*

Set wb = Workbooks.Open(filepath)

'Assuming Sheet1, Cell A2 in export file to be copied into Sheet2, Cell B2 in Expense Report
Sheet2.Cells(2, 2).Value = wb.Sheet1.Cells(2, 1).Value

wb.Close SaveChanges:=False

End Sub

If desired, code can also be designed for a search-diaolog box to pop up when the export file is to be located.