VBA to copy all data from the last sheet of one workbook into a specific worksheet of master file

Leeward904

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Hello all i apologize if this has been asked previously i wasn't having any luck finding the right macro for my need.

As a project manager i have to provide a "Contract Status Report" once a month to my customer and my supervisor fills out a daily production form, a new work sheet is added for each day, and it is always located in the same location for each job. At the end of the month i need to pull the latest daily production form and include it in my "Contract Status Report" and i have my project master file set up to retrieve the labor hours report, material cost report, apply the correct rates to the various cost elements, and the file even uses the labor report to create a "employee roster" for all employees that have worked on the job. The last this thing i need to do is write a macro to have the master file go to a cell referenced location, find the .xls file title "DCR", open it (or not it makes no difference to me), copy all data from from the last worksheet in the file (the supervisor has a bad habit of naming the sheets different names each time), copy the latest daily report to the worksheet in the master file title "Progress Report", and finally close the DCR without saving (unless it didnt open in the first place).

That was really wordy so this might make it easier:
1. Macro assigned to "button" on master file- Click
2. Master file goes to cell referenced (on master file worksheet "Data" Range("A27")) location to locate file titled "DCR.xls"
3. Find the last worksheet in DCR file and copies all data
4. paste DCR worksheet data to worksheet in master file titled "Progress Report" (preferably keeping the DCR file formatting)
5. close without saving DCR file

I am new to VBA and any help is greatly appreciated!!!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Here is a possible solution

VBA Code:
Sub upd()
Dim DCSWB As Workbook, Mas As Workbook
Dim path As String, File As String, pathAndFileName As String
Dim cnt As Long

Set Mas = ThisWorkbook
path = Sheets("Data").[A27].Value
File = "DCR.xlsx"
pathAndFileName = path & "\" & File
Set DCSWB = Application.Workbooks.Open(pathAndFileName, ReadOnly:=True)
cnt = DCSWB.Sheets.Count
DCSWB.Sheets(cnt).UsedRange.Copy
Mas.Sheets("Progress Report").Paste
DCSWB.Close False
End Sub
 
Solution

Leeward904

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
It worked!!!

Thank you for the help this will save a massive amount of time!

Is there a way to add a piece that clears the existing data from "Progress Report" and then paste the new "DCR" data? That way i do not have to manually clear the worksheet prior to importing the new data?
 

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Add this line after Set Mas = Thisworkbook

VBA Code:
Mas.Sheets("Progress Report").UsedRange.Delete
 

Leeward904

New Member
Joined
May 5, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Eureka!

you are a beast! thank you for your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,923
Members
418,253
Latest member
TheJackal26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top