Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

Sorry, that won't happen again! It was my mistake for not enclosing the proper coding around my script. Thanks for your help guys. I will try to mess with the workbook and see if I can get it working properly.

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

I already edited that part of the code to

fn = Dir(myDir & "*.xlsx*")

Here is a Zip of the tracking workbook with testbooks inside. Please keep in mind the folder I am going to be using this tracker in has many files, sometimes 500 files at a time. Do you think that will become an issue? Because as I have seen here, at the forth workbook the tracker becomes confused and brings data into the wrong cells. I am trying to make this report as non-manual as possible. Thanks so much for your assistance and help with this, I cannot thank you enough!

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

Yay! That did it! Thank you so much! You are quite exceptional with coding I am new to all of this and cannot thank you enough, you truly saved me HOURS of work lol!

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

Yes it is working, but I still have the same issue of missing two lines of important information. I am missing lines E and G from the tester workbooks when I run the tracker. Did you notice that too?

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

I got it! Here it is corrected

myRng = Array("a19", "a23:a31", "b23:b31", "c23:c31", "d23:d31", "e23:e31", "g23:g31", "h23:h31", "e11", "e12")

Thanks so much again I cannot thank you enough :)!

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

I am getting run time error 1004. It is high lighting the formula in this code

Sub test()
    Dim myDir As String, fn As String, LastR As Range, myRef As String
    Dim sn As String, myRng, i As Long, x As Long, y As Long
    myDir = ThisWorkbook.path & "\test books\"
    sn = "Sheet1"
 myRng = Array("a19", "a23:a31", "b23:b31", "c23:c31", "d23:d31", "e23:e31", "g23:g31", "h23:h31", "e11", "e12")
    fn = Dir(myDir & "*.xlsx*")
    Do While fn <> "" And fn <> ActiveWorkbook.Name
        Set LastR = Cells.Find("*", , , , 1, 2)
        If LastR Is Nothing Then Set LastR = Range("a3")
        Set LastR = Intersect(Columns(1), Rows(LastR.Offset(1).Row))
        For i = 0 To UBound(myRng)
            With Range(myRng(i))
                x = .Rows.Count
                y = .Columns.Count
            End With
            With LastR.Offset(, i + 1).Resize(x, y)
                myRef = "'" & myDir & "[" & fn & "]" & sn & _
                "'!" & Split(myRng(i), ":")(0)
                .Formula = "=if(" & myRef & "<>""""," & myRef & ","""")"
                .Value = .Value
            End With
            LastR = fn
        Next
        fn = Dir
    Loop
End Sub

The beginning of all the files I need start with "quo" which is the only difference between the true files and the test files. I'm not really sure why this works at home but not at work lol. I copied some of the actual excel files that I will be using this on and replaced the test files. I altered the sn and different little things, but it still won't work on the actual files only the testers

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

No, the workbooks are named quo date initials.xlsx (without spaces) and they do not have any apostrophes.

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

I just tested that and it copies the name of the file and then starts putting random words on the tracker from the tester books Let me try to mess around with the original one we had working. The only problem was this code right here

.Formula = "=if(" & myRef & "<>""""," & myRef & ","""")"

Maybe we can fix this somehow? I wonder why it worked perfectly in the testing phase but when trying to run it with actual workbooks it gives this error... Do you think it might be an issue with the template? To answer the question, we are all using Excel 2007 in my office.

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

That makes a message box appear, brings in only the file name. It pops up and refs each array and macro, but does not bring the data onto the tracking workbook

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

At least the error in VB is gone lol

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

I know I am running Excel 2010 here at my house on my laptop, and at work I am running Excel 2007. So that is probably the culprit here. I have zero issues at home and a million when I try to do this at work. I know you are very intelligent when it comes to this kinda thing, so I am going to take my copy of Office 2010 into work and install it onto my desktop. I will try the original code again in the tracker and use it on the 2007 files that are saved at work. I will let you know how that goes once I am at work for a little bit. Again, I seriously cannot thank you enough for all of your time and assistance with this project.

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

E:\Jamie Spotts\Todd's Sales Work\Sales Quote Excel Project\Excel\Test Tracker2\test books\[Test 1.xlsx]Sheet1'!a23 (1st msg box)
E:\Jamie Spotts\Todd's Sales Work\Sales Quote Excel Project\Excel\Test Tracker2\test books\[Test 1.xlsx]Sheet1'!b23 (2nd msg box)
E:\Jamie Spotts\Todd's Sales Work\Sales Quote Excel Project\Excel\Test Tracker2\test books\[Test 1.xlsx]Sheet1'!c23 (3rd msg box)

This continues by WB for each cell, but it does not pull any of the cell data over just the name of the workbooks.
The only thing that ever changes is the name of the test book.

I installed 2010 on my PC and I am still having the same issue with the formula I am about to tell my boss Ill just run the tracker from my laptop lol

Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook

Ok I figured it out. The macro doesn't accept the formula when I try to run the Workbook on a Shared Network drive (our E drive at work). But it will run on my PC off the C drive. Is there any way to alter the formula to be more friendly with data on our shared drive?