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

jamiespotts · Oct 5th 2011

Hello,

I am working on a Quoting project and I have the Macro working to an extent. I can copy the first file perfectly, but after that my macro just stops bringing data in from the other 4 workbooks. Attached is the macro I currently have and been editing.

I also need help with making it so the rows entered on the Tracking workbook are one after the other, would anyone know the VB code for performing something like that?

Thanks,
Jamie

trunten · Oct 6th 2011

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

Hi Jamie,

It's a bit hard to know why it's not working with your spreadsheet but my guess is the problem lies in your dashboard. Check that the cells used in the macro are actually the same as the cells that contain the worbook path.

p.s. I've tried to reduce your code a bit

Code

sub CopyFromWBs()
    Dim wbA As String, wbB As String, wbC As String
    Dim wbD As String, wbE As String, oWB As String
    Dim Day As String, Qty As String, ItemNum As String
    Dim Description As String, Size As String, Price As String
    Dim Company As String, Contact As String, SalesName As String
    Dim currentRow As Long
    Dim shtDest As Worksheet
    Dim wBook As String, wBooks(1 To 5) As String




    With Sheets("Dashboard")
        wbA = .Range("C3")
        wbB = .Range("C7")
        wbC = .Range("C11")
        wbD = .Range("C15")
        wbE = .Range("C19")
    End With
    wBooks = Array(wbA, wbB, wbC, wbD, wbE)
    'Speed
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    'Save me
    oWB = ActiveWorkbook.Name
    'Destination for sales data
     Set shtDest = ActiveWorkbook.Sheets("AllQuotingInfo")
    'Clear old
    shtDest.Range("A3:O" & Rows.Count).ClearContents
    currentRow = 3
    For Each wBook In wBooks
        If wBook <> "" Then
            'Open, copy, close
            Workbooks.Open wBook, ReadOnly:=True
            SalesName = Range("A19")
            Day = Range("F8")
            Qty = Range("A22,A23,A24")
            ItemNum = Range("B22,B23,B24")
            Description = Range("C22,C24,C25")
            Size = Range("E22,E23,E24")
            Price = Range("H22,H23,H24")
            Company = Range("E11")
            Contact = Range("E12")
            Windows(oWB).Activate
            With shtDest
                .Activate
                .Range("A" & currentRow) = SalesName
                SalesName = ""
                .Range("B" & currentRow) = Day
                Day = ""
                .Range("C" & currentRow, "C" & (currentRow + 3)) = Qty
                Qty = ""
                .Range("D" & currentRow, "D" & (currentRow + 3)) = ItemNum
                ItemNum = ""
                .Range("E" & currentRow, "E" & (currentRow + 3)) = Description
                Description = ""
                .Range("F" & currentRow, "F" & (currentRow + 3)) = Size
                Size = ""
                .Range("G" & currentRow, "G" & (currentRow + 3)) = Price
                Price = ""
                .Range("H" & currentRow) = Company
                Company = ""
                .Range("I" & currentRow) = Contact
                Contact = ""
            End With
            Windows(GetFilenameFromPath(wBook)).Close
        End If
        currentRow = currentRow + 4
    Next
    'Reset
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    'Cleanup
    Set shtDest = Nothing
End Sub

Display More

jamiespotts · Oct 6th 2011

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

Thanks for the reduction, I knew the code could have been smaller but I am pretty new to all of this! I appreciate your guidance with this project as well. When I tried to run the macro I received the error "Compile error: Can't assign to Array" on the line colored in red below:

Code

With Sheets("Dashboard")
        wbA = .Range("C3")
        wbB = .Range("C7")
        wbC = .Range("C11")
        wbD = .Range("C15")
        wbE = .Range("C19")
    End With


wBooks = Array(wbA, wbB, wbC, wbD, wbE)

I have flexibility on this project and attached a generalized copy of what I currently have (It wouldn't allow me to post a copy of the workbook??); however, I’m unable to attach an example of the actual workbooks to be transferred due to company confidentiality. This is setup to take from 5 workbooks at a time, but it would be ideal for the macro to track data from multiple workbooks found within a specified file. Can this be done instead?

This is what should be transferred from multiple workbooks into one:

Workbooks to be trans------------- Data Tracker Workbook
ColA: A19 ----------------------------- ColA: A3 (down to x rows)
Date: F8 ------------------------------- Day: B3 (down to x rows)
ColC: A22:A30 ------------------------ ColC: C3 (down to x rows)
ColD: B22:B30 ------------------ ------ColD: D3 (down to x rows)
ColE: C22:30 ------------------ --------ColE: E3 (down to x rows)
ColF: E22:E30 -------------------------ColF: F3 (down to x rows)
ColG: H22:H30 ------------------------ ColG: G3 (down to x rows)
ColH: E11 -------------------------------ColH: H3 (down to x rows)
Coli: E12 --------------------------------Coli: I3 (down to x rows)

These details should help better understand what I am up against here. I have a dashboard (willing to change) and the perfect layout between excel files. The "Tracker" workbook has to know where the next available line is when transferring data from wba-wbzz. More importantly, the Tracker cannot transfer data from the same file twice to avoid duplication.

Your time is appreciated,
Jamie Spotts

jindon · Oct 6th 2011

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

jamiespotts

Do all the files have common sheet name?

If so, you don't need to open each file.

jamiespotts · Oct 6th 2011

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

Oh really? I didn't know that, but it does that even when I only enter one file name into the first field. Can we make it so the script pulls data from all the excel files found within a specified folder?

jamiespotts · Oct 6th 2011

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

The workbook is "too big" to attach. It is 9000kbs, almost 9mb lol. I think you might be onto something about my dashboard, although I am still receiving that array error

jindon · Oct 6th 2011

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

If I looked at your posts, I found that the range you pull has different length(depth).

How do you want it ?

jamiespotts · Oct 6th 2011

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

Well that is the issue, I'd prefer if the script could tell by the data from the workbooks to be transferred how much should go onto the Tracking workbook.

Some Key Points to what I want this script to do:

Columns C-G & rows 22:30 must have entered data in the fields on files to be transferred, or else they will not be carried over to the tracking workbook
The "Tracker" workbook has to know where the next available line is when transferring data from "wba-wbzz".
More importantly, the Tracker cannot transfer data from the same file twice to avoid unwanted duplication(s).

*When I get into work at 8:30 in the morning tomorrow I am going to try to upload this workbook again. I do not know why it isn't working, I even compressed it.. I am on the east coast, so I gotta head to bed. Thanks again!

jindon · Oct 6th 2011

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

try this one

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 = "d:\test\"   '<- alter to suite
    sn = "Sheet1"  '<- alter to suite
    myRng = Array("a19", "a22:a30", "b22:b30", "c22:c30", _
    "e22:e30", "f22:f30", "h22:h30", "e11", "e12")
    fn = Dir(myDir & "*.*")
    Do While fn <> ""
        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

Display More

trunten · Oct 6th 2011

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

Sorry,

Code

Dim wBook As String, wBooks(1 To 5) As String

should've been

Code

Dim wBook, wBooks

But I've seen that jindon has come up with a much neater way anyway. I wish that guy taught a master class

jamiespotts · Oct 6th 2011

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

Thanks for your help! I am still receiving a compilation error: Sub or Function not defined. I am going to attempt to upload the workbook now so you can kinda see what I am working with first hand. We have the details, but I really want to show you two the physical workbook so you can mess with it. Download it if you can and try to mess with it. Right now after entering in the two macros, I am getting an error on "Windows(GetFilenameFromPath(wBook)).Close"

Here is a link to the Tracking Workbook after altering it with these macros on the thread:
http://www.fileswap.com/dl/Nwq…racker_Workbook.xlsm.html

Here is a link to the Tracking Workbook before I changed or updated the Macros:
http://www.fileswap.com/dl/DM0…Tracker_Workbook.zip.html

Again I cannot thank you two enough for this!!

trunten · Oct 6th 2011

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

Hi jamiespotts,

I thought you'd created your own function which provided the means for getting the file name from the path.

Anyway, I've had a look at your spreadsheet and I've amended my code slightly and added the getFilenameFromPath function. I've attached that along with my test workbooks to show how it works. Hopefully that'll sort you.

If not I've played with jindons code a bit which seems to work perfectly:

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 = "C:\Test" '<- alter to suite
    sn = "Sheet1" '<- alter to suite
    myRng = Array("a19", "a22:a30", "b22:b30", "c22:c30", _
    "e22:e30", "f22:f30", "h22:h30", "e11", "e12")
    fn = Dir(myDir & "*.xls*")
    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

Display More

jamiespotts · Oct 6th 2011

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

I'll play with it some more, but the data is not transferring over. Not quite sure what the difference is on my end.

jamiespotts · Oct 7th 2011

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

?? The workbook doesn't transfer any data, but it also doesn't have any errors.

jindon · Oct 7th 2011

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

My code has path sepatator at the end

myDir = "d:\test\" '<- alter to suite

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 = "d:\test\" '<- alter to suite
    sn = "Sheet1" '<- alter to suite
    myRng = Array("a19", "a22:a30", "b22:b30", "c22:c30", _ 
    "e22:e30", "f22:f30", "h22:h30", "e11", "e12") 
    fn = Dir(myDir & "*.*") 
    Do While fn <> "" 
        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

Display More

trunten · Oct 7th 2011

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

Sorry about that jindon I'm on a mac at home so had to change a lot to get your code to work. I was simply trying to edit it so that it only searched for excel files and ignored itself in case it too was in that folder. I guess when I changed it back to work for windows I missed that bit.

However, whilst I admit that omitting the "\" would stop it from working I would've though he'd be getting a load of #ref values not nothing?

jindon · Oct 7th 2011

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

Quote from trunten;579027

However, whilst I admit that omitting the "\" would stop it from working I would've though he'd be getting a load of #ref values not nothing?

No,

fn will be always vbNullString that is "", so it never goes into Do loop.

That means do nothing.

trunten · Oct 7th 2011

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

Of course! Thanks. I guess I should just boot camp my laptop :smile:

Anyway, to hi-jack the thread for a bit. Where on earth did you learn all of this from?

jamiespotts · Oct 7th 2011

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

Thank you so much. It works perfectly, except for when the macro runs the 4th workbook and beyond. After workbook 3 is ran through the tracker, all workbooks after that are mixing the date with the item number and data is not adding up correctly. Because you used all A's in the workbook its hard to tell where the data is actually working and not working when it is being transferred. None of the data found under item number is being carried over from the first 3 workbooks at all. It is showing on the tracker starting at workbook 4 the item number, but we lose the date completely.. I noticed that since the file name is carried into the tracker under column A now instead of name being in column A (which I definitely like being shown in the report so thank you :), maybe we have to change some kind of formula to compensate for the difference in columns, due to it adding in the file name. On your test sheets, I would add specific data into the correct cells on empty workbooks so you can follow what I mean.

Anymore help on this would be great! I am so glad that this is almost done! It does a great job aside from the minor transfer issue.

AAE · Oct 7th 2011

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

Jamie,

Please learn to use code tags.

Re: post #3, lack of code tags.

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
I've added the tags for you this time only. Be sure to use them in future posts.

[COLOR="navy"]How to use code tags[/COLOR]

[noparse]

Code

[/noparse]
[COLOR="navy"]your code goes between these tags[/COLOR]
[noparse]

[/noparse]

Thanks.

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

Participate now!

Share