AnnieLox

New Member
Joined
Sep 18, 2017
Messages
22
hello all! i am learning to code, and have 7 books but still have such a hard time understanding loops. i'm desperate for help.
My boss wants me to loop through a years worth of files and pull data. I tried for days, and am just not retaining the rules behind the action. I hope someone will just build for me in the meantime. I promise i'm not lazy.

this is what i need each file to perform. Each file has the same sheet names.
i need to open each sheet, add a totals sheet, and then copy that information in a separate sheet after the last row of data that was collected. i hope at the end to have the full years totals.
the problem is i have 12 folders with 30 files - each with a different date.
I'm not understanding how to make an array understand what file to open and then keep that file name to perform the actions.


Code:
Sub TEST1()
Dim OpenPath As String
Dim OpenName As String
Dim wb As Workbook
Dim J As Integer
Dim Newname As String
Dim cell As Range
Dim LastRow As Long






'    Windows("numbers for management.xlsx").Activate
'    Set destRng = Sheets("TOTALS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


''-------- below is the repeat
  
Sheets("Data").ShowAllData






 With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "TOTALS"
    End With


    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "File Name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Invoices Registered"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Total Open"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Total Invoices"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "DataInt"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "DataExt"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Sum"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "BackInt"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "BackExt"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Sum"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Assigned"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=Data!RC[19]"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(Data!C[3])"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(Data!C[21])"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(Data!C[21])"
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=backup!C[18]"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(backup!C[18])"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=COUNT(backup!C[18])"
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("L2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-1]"
   ' Range("A2:L2").Select
   ' Selection.Copy
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:L" & LastRow).Copy Destination:=destRng


  End If
'Next wb




End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
To help you better, you need to explain with examples what you need to do.
Describe step by step what you should do with each file.
If you can, upload some files to the cloud to explain your process.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Thank you Dante! I have opened an account and uploaded the files.
https://www.dropbox.com/sh/64ze3ff8mljn41k/AABHwLvZ90d6mRCxhlSLY3Ola?dl=0

The file structure i know how to edit in code very well. so anything helping i may be able to work with.
Also, the date is set up in EU because our corp. is located there.
so the files are located here, further separated by month
T:\COST CONTROL HOU - Reports\Daily Reporting\PI Report\2019
further breakdown of file structure:
T:\COST CONTROL HOU - Reports\Daily Reporting\PI Report\2019\01 January\01.01.2019 HOU
\01 January\02.01.2019 HOU
\02 February\01.02.2019 HOU
\02 February\04.02.2019 HOU
\02 February\05.02.2019 HOU

i also added into the dropbox the code i use to move our files, i was trying to adjust it for this same purpose.

i wrote the code in "Numbers for management"
i curently have the code to automate if i press start inside each file. but i want it to loop and automate all of the files.

i need it to open each file, create a tab (totals)
Create some totals from the Data Tab and Backup Tab
Copy those totals over to the Number for Management file
Close - i dont care if it saves.

PLEASE help - i have no pride here lol.
 
Upvote 0
I already have your files, but there is no explanation of your need.
I have no idea where to start.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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
Back
Top