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,922
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,922
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,078,462
Messages
5,340,456
Members
399,376
Latest member
Tresfjording

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top