Consolidation not working when moving file!!

Hi Shaun,

Not much to go on here, but I note that all the files you are accessing are on your C drive. Once you move the macro to the S drive (my assumption there for the shared drive), your macro will not be able to find the files. You need to have them on the same computer - preferably in the same folder.

Having said that, the code doesn't seem to be very efficient! Lots of scrolling. What are you trying to achieve? You seem to be trying to add together the value in several columns on row 56 of the same sheet.

This is quick and nasty, but assuming you want to put the consolidated data in H56 of the sheet with the macro, and get the value from the other sheet, this should do it a bit more efficiently.

NOT TESTED. You may have to tweek it a bit, bit have a go and see how it works.

Please Login or Register  to view this content.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David,

Thanks for your reply.

The code is very messy and there is lots of scrolling because I manually made this macro by using the record macro feature as I'm still learning excel. However it works perfectly for what I need. (only on my PC though!)

Basically what I am trying to do is work related. It is hard to explain but I will give it a shot:

I get a excel spreadsheet with a heap of data that contains lengths of material that needs to be cut, in this case metal tube. It has to be manipulated so it can go out in to the factory for the machine. Also note that every sheet I get sent to me is different. It can be up to 50 rows and 50 columns worth so doing it manually would take forever (which is what I did the first time to record the macro). Now what I need to do is separately consolidate each column to eliminate any duplicates so I don't have a certain length appearing 10 times, but instead only once with the the quantity of 10.

It has to be consolidated in to separate columns as the they material has markings. For example I get a heap of sizes with the markings W1, then another lot W2 etc. So these then become the different columns that need separate consolidating.

I hope that made sense

So is there anyway to change the source of where the macro runs from so I can move it to the shared drive? I can also insert the full code....lots of scrolling though as what I showed before was only part of it.

Thanks heaps

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Shaun,

If you can attach an example of the workbook you are trying to pull data from it would help.

I assume the macro you recorded is within the sheet you are trying to consolidate from!

What is it you are looking for as you scroll around?

Please attach the workbook, and give some explanation. I think the number of lines in the macro can be dramatically reduced.

For the macro to work properly, it should be in a separate workbook so that it can work with all the different files you said you receive.

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

ROLL FORM PROGRAM.xlsm

Here is the workbook attached David.

Just wondering if there is a way I can move the source file to the shared drive so it can be accessed by all computers?

Thanks
Shaun

Thanks Shaun,

I can run all your macros, but they don't appear to be doing anything!

Since there is no data in the model you sent, I can't see what you're trying to achieve.

None of the buttons seem to do anything.

Can you send a completed file that has worked so that I can see what you're doing?

Thanks,

David

Hi David,
Thanks for your help, I managed to figure it out. I just had to change the source in every line from this:

Selection.Consolidate Sources:= _
"'C:\Users\shaun.jones\Desktop\[ROLL FORM PROGRAM.xlsm]14519 - 75x40x1.6 CYCLONIC STUD'!R2C132:R50C133" _
, Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

To my shared drive where the new file is:

Selection.Consolidate Sources:= _
"'S\CI\[ROLL FORM PROGRAM.xlsm]14519 - 75x40x1.6 CYCLONIC STUD'!R2C132:R50C133" _
, Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

Just a quick question, can I delete all those lines that are just scrolling?

Shaun,

In answer to your question. YES. I believe you can delete all the scroll lines, they are just recording arrow key use or mouse clicks.

Well done! That is virtually what I had to do to test it.

I distracted myself in trying to get a better macro than the recorded version.


If you want to make some improvements, get in touch.

All the best!

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".