Copy/update data from one workbook to another everyday automatically

[abby123]

I have a daily report that is published every morning - we'll call it "Capital Programs Report". From this report there are 388 columns of data that is ever changing. I need to create my own Tracker to consolidate this data down to about 30 columns that I care about, so I can work off of this information. How do I do this??? The data in the rows will be everchanging - the columns are going to be the headings that stay consistent. The daily "Capital Programs Report" is an emailed Excel workbook to me everyday. I can save this workbook every morning in the same spot with the same name, etc, if needed.
Let me know if you need any more information.

 

[tonyyy]

abby123,

Welcome to the Board.

You might consider the following...

Sub CopyColumns_1026396()
Application.ScreenUpdating = False
Dim fName As String
Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet, rng As Range

fName = "C:\Docs 2017\2017 Gigs\MrExcel\Capital Programs Report.xlsx" 'Change to your file path
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(Filename:=fName)

With wb2.Sheets("Sheet1")
    Set rng = Union(.Columns("A"), .Columns("C:E"), .Columns("H"), .Columns("J:M")) 'Change to match your columns
End With

Set ws = wb1.Sheets.Add(after:=Sheets(Sheets.Count))
rng.Copy Destination:=ws.Range("A1")
wb2.Close savechanges:=False
Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[abby123]

tonyyy,
Thank you for your response!!
1)Can you explain step by step where i put this code into excel?
2)Can you re-show me this code example using the first 4 columns (respectively) in my Capital Programs Report (Search Ring, Site ID, Build Status, Plan Type Desc) AND if my path where the Capital Programs Report is saved Desktop>CODenver>Daily Reports>Capital Programs. So I can see exactly how to do this?
3) Every time I open my "Tracker" will it update automatically off of the most recently saved "Capital Programs Report"? Or does the Capital Programs Report have to be open as well?

Thank you!!!!
Abby123

 

[tonyyy]

1)Can you explain step by step where i put this code into excel?

Please see the Contextures website for step by step instructions.

2)Can you re-show me this code example using the first 4 columns (respectively) in my Capital Programs Report (Search Ring, Site ID, Build Status, Plan Type Desc) AND if my path where the Capital Programs Report is saved Desktop>CODenver>Daily Reports>Capital Programs. So I can see exactly how to do this?

Your original post stated "the columns are going to be the headings that stay consistent" - so we really don't need the column header names, just the column letters. In this line of code...

Set rng = Union(.Columns("A"), .Columns("C:E"), .Columns("H"), .Columns("J:M"))

...change the column letters to match your columns. If you want the first four columns, the line will be...

Set rng = Union(.Columns("A:D"))

The path (ie, fName) on your desktop should look like the following:

"C:\Users\YourUserName\Desktop\CODenver\Daily Reports\Capital Programs\Capital Programs Report.xlsx"

3) Every time I open my "Tracker" will it update automatically off of the most recently saved "Capital Programs Report"? Or does the Capital Programs Report have to be open as well?

Please use this code...

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim fName As String
Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet, rng As Range

fName = "C:\Users\[I]YourUserName[/I]\Desktop\CODenver\Daily Reports\Capital Programs\Capital Programs Report.xlsx" 'Change to your user name
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(Filename:=fName)

With wb2.Sheets("Sheet1")
    Set rng = Union(.Columns("A"), .Columns("C:E"), .Columns("H"), .Columns("J:M")) 'Change to match your columns
End With

Set ws = wb1.Sheets.Add(after:=Sheets(Sheets.Count))
rng.Copy Destination:=ws.Range("A1")
wb2.Close savechanges:=False
Application.ScreenUpdating = True
End Sub

The code will run every time the workbook is opened. It will then open the Capital Programs Report, copy the columns to a new sheet in the Tracker workbook, and close the Capital Programs Report.

 

[abby123]

tonny,

Thanks for your response. I should mention the column "location" will change, however the column heading will stay consistent. For example, one day "Zoning Approved" might be Column F, the next day the report is published, "Zoning Approved" might be column E. How do I do this off of the column "name"???

Thanks!

 

[abby123]

tonny,
Also, I am getting an error based on the code you gave me (the first code):
It says "Run Time Error '9':

Subscript Out of Range

The second code you gave me in this thread won't populate when i go to run the "macro"

Thanks!!

Abby123