VBA code amendment required for updated workbook format

Hello

My workbook has old code which did achieved what I still want to achieve however the format of the workbook/sheets has now expanded/changed so I am looking for the code to be amended (or new code if required).

The aim is to pull data from 8 sheets (W1>8) and show in a new sheet (manpower import) in the relevant order and columns. The order should be in date and in earliest time (start).

In sheet (manpower import) I have put an example of the desired result (columns L-Q), which should appear in columns B to G. The import button is existing from the old solution.

If any sheets need to be unlocked the pw for this example doc is open.

Help would be very very appreciated, as I unfortunately just don't understand code!!

I have deleted other sheets in the interest of size for upload but the workbook will contain more sheets if that makes any difference-however I can keep the sheet names as they are.

Hi Marc L

Thank you for taking the time to look into this.

What useless column sorry?
I can't rename sheets 7 and 8 because of a formula which this change would take overt the excel formula character limit.
I have attached the expected result in a example table (columns L-Q)?

Hi Sintek

Thank you for looking at this!

I can't rename sheets 7 and 8 because of a formula which this change would take overt the excel formula character limit.
The 20 results are correct?

Hi beyond Excel

Thank you for your help!!

I will look in to this now and reply!!

Sorry, but what are graphical objects? and I don't understand *reconvert your images into less "giant" objects* ? Sorry for being dumb!! I assume you are referring to images in cells D3 in W1>8 sheets?

Thanks

E

Originally Posted by beyond Excel

Hello. About the attached model I suggest you:

a) Replace the 'MANPOWER IMPORT' sheet of your original model and replace it with the one attached here since the table already has the appropriate formats and the macro is in the sheet itself.

b) The data summary is obtained from the sheets between 'FRONT PAGE' and 'MANPOWER IMPORT'. So you don't need to change the names of the sheets and they can have any name (they don't need to start with "W").

c) Finally, I would like to point out that what I attached is 4 times smaller than what you uploaded to the Forum. What's the big difference?... I removed the "graphical" objects.
I suggest you try to reconvert your images into less "giant" objects.

Please Login or Register  to view this content.

Hi beyond Excel

When I do this it says;

"Compile error:

Sub or Function not defined"

Hi Sintek

Thank you so much!

When I put this code in to the actual workbook it shows a run time error? If i click on * dubug * it highlights * .Range("B3").Resize(x, 6).Value = Temp *?

Ignore--this is my fault!!! There was no data for it to pull from, but after entering data it works perfectly - I just need to assign it it a button but it works from the developer run button 🥳

Thank you so so much!! and thank you everyone else for your time and efforts also - so very much appreciated!!

Originally Posted by sintek

See red snippet added above...

Not in sample file...

Hi Sintek

Thanks I will add that in

They are in sample file? there should be 8 sheets in total (W1,W2,W3,W4,W5,W6,7,8) Sheets 7 and 8 aren't called W7 and W8 due to a different formula which these characters would take it over the character limit

Also; on sheet W6 it seems to be doubling the values but it doesn't happen on any of the other sheets that I can see?

Thanks

Originally Posted by beyond Excel

Let's go by parts:
- The workbook that I showed you in post #4: Does it work correctly?...

Hi beyond Excel.

No unfortunatly not. It says

"Compile error:

Sub or Function not defined"

and then the debugger highlights; Sub Macro8()

Originally Posted by sintek

I know...What I am saying is...It is not ignoring it in the sample file...
It works as it should, even with adding extra entries into sheet 7 & 8
Perhaps you should upload another sample file with more entries and show expected result...

Hi Sintek

I have tried to put a screen recording in to a word doc to allow you to see current process - let me know if this works? (somewhat of a challenge trying to upload a video here)

Thanks

E

Originally Posted by beyond Excel

Remember I told you to replace the 'MANPOWER IMPORT' sheet from your actual workbook with the 'MANPOWER IMPORT' sheet from the workbook I showed you in post #4: it looks like you didn't.

Hi Beyond Excel

I did this by copying the sheet across workbooks and deleting the original sheet? Is this what you mean? If yes, then it shows the compile error

Originally Posted by sintek

No...See post 17....As per sample file...all works as it should...Upload new showing issue...Can only be that your actual file does not represent as your sample file shows...

Hi Sintek

Apologies, you are right it does work in the sample file however sheets 7 and 8 have been renamed to W7 and W8 which I can't do due to a character limit in a other excel formula. I assume it is the naming which is stopping this working here?

Thanks
E

Originally Posted by beyond Excel

The image you show in post #22 indicates that there is a sheet called 'QUOTE' between 'FRONT PAGE' and 'W1'.
That sheet: Does it have the same structure as W1, W2, etc or not?...

Remember that in my help the sheets between 'FRONT PAGE' and 'MANPOWER IMPORT' are processed. If that 'QUOTE' sheet does not have the same structure, it is you who is generating the error.

Hi Beyond Excel

I have no doubt it is me, but i believe I have followed the instructions? Please see attached screen recording

Thanks

E

Originally Posted by sintek

See post 17 & 23...Like I said...If actual file is same setup as sample file then the code as you already commented as working...should work...
I have used your original file without changing sheet names, inserted more time slots in the other sheets W1-W6, 7, & 8 and all works as it should with code supplied in Post 9...
Gonna go around in circles...

Hi Sintek

I do apologise but I don't know where I am going wrong? Sheets W1>8 are identical in setup. Using your code Sheets W1>W4 seem to work perfectly, W5&W6 duplicate results, 7 and 8 result in an error? I have tried to show this in the attached screen recording. Thank you for being so patient!!

I'd love to but file size and upload limits...

Helloo

I have managed to get it down to size by removing 13sheets which sit AFTER sheet "Manpower Import". I have also removed some images (from D3 on sheets W1>8)but I assume that won't affect any code.

I can swap front page / quote page sheet positions if required.

Thank you...I know my level of understanding is very unhelpful!!

Best

E

Originally Posted by sintek

This does not help at all...You have uploaded a sample file with no entries in any of the sheets and have shown no expected outcome???

Apologies...I have now included an example table of outcomes in sheet manpower import using data from Sheets W1, W5 and 7 out of the 8 sheets in total (W1>8).

Is this sufficient?

Thanks

E

Originally Posted by sintek

Harea...You mentioned previously above that the code only works for some sheets and errors with others...
So ideally...If you are wanting us to solve this...Then surely you need to do a bit of work...
So...upload a sample file with multiple entries in ALL the sheets W1-W6 & 7 & 8 and show expected outcome...This way we are able to ascertain what the issue is...If any...
Understand?

Hi Sintek

Please find attached. Hope this is what you need?

Thanks

E

Originally Posted by sintek

Oh my word harea...My bad...
Did not change the sheet no to STRING...Would have picked it up if data existed...
This gives exact same output...

I don't know what this means but thank you so so much!!! all seems to be working perfectly!!!

Is there anything I need to know? (not change sheet order etc?)

I am so appreciative of your patience and efforts!!!

Thanks you

E