Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Todd's Sales Work
Apostrophe...
Can you change
to
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Todd's Sales Work
Apostrophe...
Can you change
to
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
I actually tried to use it in the folder that the real workbooks are currently saved in that does not contain any characters. You are seeing the last time I tried to use the script in a smaller folder off the same drive. I copy pasted my shared directory instead of the actual folder on our shared network. But I wanted you to see that it was on the E drive not C drive, my co-worker who is the IT manager said that when he tried running a macro on the E drive off of his computer it did not work. He had to connect to the network through remote desktop for his macro to run through the folder on the e drive. I haven't tried this theory yet, I work on multiple projects and a few new ones that have come my way. The original script you provided works by bringing the files into a specified area on my local drive, so I figure I'll remain doing it this way for now. It is way better than manually entering information on reports, so I am super thankful you were able to assist me as you have here.
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
OK, when it comes to network, there would be certain restrictions, so better leave it to IT person.
Good luck.
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Yes, definitely better to hand this part off to him and let him teach me how to run it from the network at a later time. Again, I cannot thank you enough you are very resourceful and quick to respond. I will definitely look for you in the future on any other VB assistance I might need, as long as that is alright with you! Have a great weekend
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Hey jindon
One column on the "testing" workbooks now has a list for users to choose from and the tracking workbook only displays the name of the file. It starts entering the names of two items from the list and blank cells. Would you possibly know why would that happen? I can't seem to figure out why. Do I have to definte the name of the list instead of the row in the macro? If so, what would I put instead? I would definitely appreciate any assistance. Everything works fine up until the list on the documents came into effect.
Thanks,
Jamie
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Quote from jamiespotts;580623
One column on the "testing" workbooks now has a list for users to choose from and the tracking workbook only displays the name of the file.
I don't understand the situation now.
Can you upload a files again?
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Yes let me replicate them for you really fast. Ill be done in a second.
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Here you go. Column H is where the list is and the range is named "units"
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
thanks for the help in advance
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
Here's a result from the code.
Is anything wrong?
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
My mistake! Got it
No problems anymore, thanks!
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
I still have that same error. No, there aren't any apostrophes
Here is the script
[vb]
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", "f7", "a22:a30", "b22:b30", "c22:c30", "e22:e30", "g22:30", "h22:h30", "e10", "e11")
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))
[/vb]
The bottom line is the error. Could this be because of the data validation list in column H?
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
You are missing column letter
myRng = Array("a19", "f7", "a22:a30", "b22:b30", "c22:c30", "e22:e30", "g22:g30", "h22:h30", "e10", "e11")
and just delete And fn <> ActiveWorkbook.Name, there's no need for it.
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
I am so tired, it is 12 am for me right now. I have been up since 6 in the morning! Figured it was something overlooked by myself lol Thanks again.
Re: Copy Data from Specified Areas in 5 Workbooks & bring into 1 Tracking Workbook
You know what?
The files I donwloaded has column letter properly....
Don’t have an account yet? Register yourself now and be a part of our community!