Code to pull row 'B-R' to another sheet based on a number range of one cell

[Celliott89]

I have a workbook with 5 tabs.

Summary - Labor Data Sheet - Paste Report - Instructions - All Codes

The idea is we pull a report from Foundations and paste it in the paste report Tab.

Then based on the Cost code in column D, that entire row will be copied and pasted in the Labor Data sheet in its appropriate area.

The codes are all listed in the All Codes Tab- so example:

Codes : 11000- 22099 - Are all Labor codes and so they will need to do to the Labor section of the Labor Data Sheet.
Codes : 50000- 50099 - Are all Equipment codes and so they will need to do to the Equipment section of the Labor Data Sheet.

The issue is the report is not always the same length- some projects will have 3 labor codes and others can have 50.

The other issue is the report always carries over a blank and total line- which do not have cost codes- and are NOT needed so the best column to use is the cost code column.

I have tried every formula I can think of but I think sense its based on a range of numbers, and the row from B - R needs to be pulled over; that maybe a VBA code would solve the issue? please help!!! Example Sheet Attached - with the report pasted into the labor data sheet how it should look.

[vba_php]

I think folks here would be a little unclear about the question you are asking. based on the last statement of yours, I think you said:

rows B - R need to be pulled over from the pasted report to the ''LABOR DATA SHEET'' tab, right? or is that just part of it? there is also something to say here, coming strictly from a business person:

you are aware, aren't you, that most software from 3rd vendors, like Foundations for instance (I'm only assuming with regard to Foundations), almost never take into account other programs and how they are built and what kinds of issues that would cause if you, the developer, have to use other programs to satisfy management requests. I've used 3rd party software many times, bought by corporations, and most of the time I did, I ended up using so many other programs to satisfy management that I essentially told a lot of them that their software purchase was kind of a waste of money. I'm not telling you to say that obviously, but I'm rather possibly giving you an idea of how you could better your position and gain some kudos with management? You're working in a construction company, right?

I developed a software solution for a small business a long time ago, and their primary complaint was that the software they bought from a 3rd party corporation was outputting database records that were not even within the date ranges specified by the users. And when the president of the business called the vendor to complain, the response from the tech was: well, we don't make mistakes. you must be mistaken. give it another try.. And I was called in and shown that there was no user error.

At any rate, back on topic. Can you answer my initial question? is that what you're asking about? or is there more to it?

[vcoolio]

Hello Celliott89,

Just another item to bring to your attention which I'm not too sure about.

The Labour codes 11000-22099 also encompass material codes,e.g. 21010 but, based on your sample workbook, these should be placed in the Material section. Could you clarify this please.

Cheerio,
vcoolio.

[Celliott89]

I need rows columns B-R to be pulled to the labor section of the labor data sheet - IF the cost code is 11000-22099

Likewise for the other sections - based on the cost code ranges provided in the all codes tab.

The issue I think is the raw data is broken up by a blank and subtotal line, which are not needed.

I hope that answers it.

I think folks here would be a little unclear about the question you are asking. based on the last statement of yours, I think you said:

rows B - R need to be pulled over from the pasted report to the ''LABOR DATA SHEET'' tab, right? or is that just part of it? there is also something to say here, coming strictly from a business person:

you are aware, aren't you, that most software from 3rd vendors, like Foundations for instance (I'm only assuming with regard to Foundations), almost never take into account other programs and how they are built and what kinds of issues that would cause if you, the developer, have to use other programs to satisfy management requests. I've used 3rd party software many times, bought by corporations, and most of the time I did, I ended up using so many other programs to satisfy management that I essentially told a lot of them that their software purchase was kind of a waste of money. I'm not telling you to say that obviously, but I'm rather possibly giving you an idea of how you could better your position and gain some kudos with management? You're working in a construction company, right?

I developed a software solution for a small business a long time ago, and their primary complaint was that the software they bought from a 3rd party corporation was outputting database records that were not even within the date ranges specified by the users. And when the president of the business called the vendor to complain, the response from the tech was: well, we don't make mistakes. you must be mistaken. give it another try.. And I was called in and shown that there was no user error.

At any rate, back on topic. Can you answer my initial question? is that what you're asking about? or is there more to it?

[Celliott89]

I didn’t catch that 😩 I didn’t set up the cost codes however I think the ranges I set 11000-22099 could work, if i change that material code in the system. I think those are old codes- because the material code we currently use is in the 40000s range

Hello Celliott89,

Just another item to bring to your attention which I'm not too sure about.

The Labour codes 11000-22099 also encompass material codes,e.g. 21010 but, based on your sample workbook, these should be placed in the Material section. Could you clarify this please.

Cheerio,
vcoolio.

[vba_php]

I need rows columns B-R to be pulled to the labor section of the labor data sheet - IF the cost code is 11000-22099

Likewise for the other sections - based on the cost code ranges provided in the all codes tab.

The issue I think is the raw data is broken up by a blank and subtotal line, which are not needed.

I hope that answers it.

i bet you can do this functions through the interface, but I never use them. how much do you know about coding? the fact that there are blank lines on the tab paste report is irrelevant. code can ignore those in many ways. do you know how to record a macro and learn from the code that excel produces? you could easily do that in this case by clicking buttons and then the code that you get from the program would not have to be modified at all really. do you want to give that a try? why not, and post another time when you get stuck? that would be a good learning experience, I would think.

just a suggestion.

[Celliott89]

That is what my uncle suggested until he realized that the reports are never the same -

The blank line could be row 13 on one report and row 34 on another - this is because the different projects have different ways of breaking up the budget on different cost codes

i bet you can do this functions through the interface, but I never use them. how much do you know about coding? the fact that there are blank lines on the tab paste report is irrelevant. code can ignore those in many ways. do you know how to record a macro and learn from the code that excel produces? you could easily do that in this case by clicking buttons and then the code that you get from the program would not have to be modified at all really. do you want to give that a try? why not, and post another time when you get stuck? that would be a good learning experience, I would think.

just a suggestion.

[nankw83]

Hi Celliott89,

I've written the below code but noticed some differences between my code & your expected results which are

1. As mentioned by vcoolio, I had a difference under Labor section due to some Material codes classified under Labor in your 'ALL CODES' sheet such as 21010, 21019, 21021, 21024 ... etc.
2. Codes 60004 & 60020 are classified as Overhead category in your 'ALL CODES' sheet but in your output they're under Labor section
3. You have sections for Material & Supervision in your 'Labor Data Sheet' but I didn't find these categories in your 'ALL CODES' sheet

Anyway, give the below code a run & if you need any explanation, I will be happy to do so or let me know if I can modify it to fit your needs

Please Login or Register  to view this content.