0

I have code to loop through several sheets of data.

Dim MyFile As String
Dim erow
MyFile = Dir("C:\My Documents\Tester")

Workbooks.Open ("C:\My Docments\Tester\TestLog.xlsm")

Sheets("Master").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Application.DisplayAlerts = False

Do While Len(MyFile) > 0
  If MyFile = "ZMaster - Call Log.xlsm" Then
    Exit Sub
  End If

  Workbooks.Open (MyFile)
  Application.DisplayAlerts = False
  Sheets("Calls").Activate
  Range("A2:P2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy

  ActiveWindow.Close savechanges:=False

  erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  ActiveSheet.Paste Destination:=Worksheets("Master").Range(Cells(erow, 1), Cells(erow, 16))

I have two issues.

Firstly the macro fails unless the first workbook in the loop was "Saved As" by myself. Not Saved only Saved As. If I open the first workbook, click save as under the same file name then run the macro it works. I have developed a work around by the macro opening the first workbook and saving as.

Second and most importantly. My sub workbooks all have the date in English format. However when pasting to the Zmaster it is coming across as 12/01/16 rather than 01/12/16.

Community
  • 1
  • 1
MBrann
  • 223
  • 5
  • 23
  • Just to clarify my date issue in my sub workbooks the date format is =NOW which is DD/MM/YY HH/MM/SS however when pasting this across into the master sheet which was working fine for 10 days on the first of December it is pasting MM/DD/YY – MBrann Dec 01 '16 at 13:06
  • 1
    Since you are dealing with multiple workbooks, removing activate and select/selection from your code and qualifying everything will make things easier to debug and follow. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Rdster Dec 01 '16 at 13:33

1 Answers1

0

I added my "sift through multiple files in a folder" script i use over and over again.

Also instead of copy pasting see how a move data around

 Sub Theloopofloops()

 Dim wbk As Workbook
 Dim Filename As String
 Dim path As String
 Dim rCell As Range
 Dim rRng As Range
 Dim wsO As Worksheet
 Dim sheet As Worksheet


 path = "pathtofile(s)" & "\"
 Filename = Dir(path & "*.xl??")
 Set wsO = ThisWorkbook.Sheets("Sheet1") 'included in case you need to differentiate_
              between workbooks i.e currently opened workbook vs workbook containing code

 Do While Len(Filename) > 0
     DoEvents
     Set wbk = Workbooks.Open(path & Filename, True, True)
         For Each sheet In ActiveWorkbook.Worksheets  'this needs to be adjusted for specifiying sheets. Repeat loop for each sheet so thats on a per sheet basis
                Set rRng = sheet.Range("a1:a1000") 'OBV needs to be changed
                For Each rCell In rRng.Cells
                If rCell <> "" And rCell.Value <> vbNullString And rCell.Value <> 0 Then

                   'code that does stuff
                    wsO.Cells(wsO.Rows.count, 1).End(xlUp).Offset(1, 0).Value = rCell
                    wsO.Cells(wsO.Rows.count, 1).End(xlUp).Offset(0, 1).Value = rCell.Offset(0, -1)
                    wsO.Cells(wsO.Rows.count, 1).End(xlUp).Offset(0, 2).Value = Mid(Right(ActiveWorkbook.FullName, 15), 1, 10)

                End If
                Next rCell
         Next sheet
     wbk.Close False
     Filename = Dir
 Loop
 End Sub
Graham
  • 7,431
  • 18
  • 59
  • 84
Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • I did a test and the code worked a treat. However in live environment it is failing. I believe this is because of the sheet it is looking to draw the data from. In the sub sheets they all have a sheet named ("Calls") in the live environment it does not seem to be getting this data. Are you able to assist at all please? – MBrann Dec 05 '16 at 13:31
  • @MBrann what are the names of the sheets in the live/prod environment? – Doug Coats Dec 05 '16 at 14:16
  • In the live environment the wkbk is called "Call Logger - Name" and they each contain 4 sheets. "Lookup", "Detail", "Summary" and "Calls". I am looking to take the data from "Calls" Range A2:A10000 – MBrann Dec 05 '16 at 14:27
  • Well make sure you path to files is correct. Also, in the sheet you want to write the data too, make sure you are referencing it correctly – Doug Coats Dec 05 '16 at 14:36
  • Cheers Doug I have checked this all appears to be in order in terms of path and files. I had made one error in the sheet I wanted to write the data too, that was corrected however, but it still does not take the data from the sub workbooks into this Mastersheet – MBrann Dec 05 '16 at 14:38
  • Something must be going on on your end. I think I need more detail to trouble shoot this for you. Can you provide more detail? – Doug Coats Dec 05 '16 at 14:46
  • @MBrann sorry forgot to tag you – Doug Coats Dec 05 '16 at 15:04
  • No worries. Sorry about the delay. I have been trying to troubleshoot the issue. So currently it is working to a degree. I believe the issue lies here....For Each sheet In ActiveWorkbook.Worksheets... It is taking the data from "Calls" which is what I need it to do, but it is also taking data from Detail and Lookup... adding this underneath the "Calls" data... How it works is I have 11 users each recording data via a userform all going to their individual sheets. When 1 entry is logged it is saved automatically. I have created a mastersheet to copy all of this data into a mastersheet. – MBrann Dec 05 '16 at 15:35
  • The Mastersheet looks in the "Calls" tab of all users and copies it into the master workbook. The Call logger "Calls" tab has 15 columns of data. I am now able to get all of the data however, I am getting extra data I do not need, such as the Lookup tab – MBrann Dec 05 '16 at 15:37
  • Well just take out the loop that goes through each sheet, or statically check for sheets by name (if you need to look at more than one sheet). Does that make sense? – Doug Coats Dec 05 '16 at 16:10
  • @MBrann hopefully this will help to some degree :) – Doug Coats Dec 05 '16 at 16:11