Help adding loop

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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
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.
 

AnnieLox

New Member
Joined
Sep 18, 2017
Messages
22
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,708
Office Version
2007
Platform
Windows
I already have your files, but there is no explanation of your need.
I have no idea where to start.
 

Forum statistics

Threads
1,077,776
Messages
5,336,183
Members
399,069
Latest member
haxahid

Some videos you may like

This Week's Hot Topics

Top