E
evoxfan
2003 Excel
Big picture:
I want to create a budget (with a macro) from an Excel file I receive from
another department. I would like to save a blank budget file that can be
copied and used for different projects where I copy the worksheet I recieve
into a "blank budget file" and activate the macro to create the budget.
What I receive to convert (project file):
This project file has one worksheet that I am interested in.
On this worksheet, I only want information from Columns A through F.
There are headings for colums in row 7 with data starting with row 8.
Headings:
A7:SECTION
B7
ESCRIPTION
D7:RESPONSIBILITY
E7:COST
F7:SUB COST
Data below headings:
Section column(Column A):
Every row is either blank, contains a five digit number, contains a five
digit number with an apostrophe at the beginning, or contains "NO SPEC".
Description column(Column B-C):
This column of data is made up of columns B & C. Every row contains a blank
or text. Column B is the text description of the number section listed in
column A. Rows within Column C will have blanks or text as well. When text is
listed in column C, it is considered a sub category of the text listed in
column B.
Responsibility Column(Column D):
Contains blanks or text of the name of the assigned party.
Cost column(Column E):
Contains a number (dollar value), blanks, or text.
Subcost column(Column F):
Contains a number (dollar value), blanks, or text.
Information I do not want:
There is also general information describing the project in rows 1-5 that I
am not interested in.
There is also additional information starting in column G through N I don't
want.
There are also formulas in cells at the bottom of this data for summary(the
row this summary data starts in varies project to project but this
particular project starts in row 888 but another project could start in row
2000), but I am not interested in this summary data.
What I hope to achieve if possible:
A template file that creates budgets from any project file I receive.
I would like to be able to copy the worksheet from the project file into the
blank budget file where the macro will pull only the information I am
interested in from the project worksheet and puts it on another worksheet in
the format listed below.
Budget file columns:
Columns:
Account Code:
This colum only needs data from the section column. The section number will
be the account code, any blanks shoul be ignored, and the No Specs should be
included if assigned in value from the cost or subcost column.
Account description:
This should relate to the account code column with text describing it. This
data should come from the description column in column B of the project file.
Responsibilty:
This should correlate with the section number and description. It should
come from the responsibility column from the project file.
Labor:
This column should be a dollar value for all the labor or installation. Its
data comes from cost or subcost of the project sheet. Anywhere there is a
text "labor" or "installation" found in the project description column, it
should pull the value from cost or sub cost ccolumn and place in this budget
material column.
Material:
This column should be a dollar value for all the material. Its data comes
from cost or subcost of the project sheet. Anywhere there is a text
"material" or "materials" found in the project description column, it should
pull the value from cost or sub cost ccolumn and place in this budget
material column.
Subcontract:
This column should be a dollar value for all subcontracts. It should pull
any remaining values from the subcost project column. It value should have
its own row with corresponding data in the previous columns.
Other:
This column should be a dollar value of all remaining cost from the in the
project cost column. There should also be corresponding data in previous
columns.
Total:
This columns should just sum up the values of all previous columns listed.
This is the last column I need.
Additional data - I would like to sum up the total column at the bottom of
the data. Also, put a border around all of the data and bold border around
the column headings.
Although I can record macros and open VB, I cannot write code from scratch,
especially to accomplish this task. This is the best solution to what I am
trying to accomplish that I could come up with, any other suggestions are
welcome.
Please let me know if you need any additional information.
Thanks in advance for reading my dilemma and any help is greatly appreciated.
Big picture:
I want to create a budget (with a macro) from an Excel file I receive from
another department. I would like to save a blank budget file that can be
copied and used for different projects where I copy the worksheet I recieve
into a "blank budget file" and activate the macro to create the budget.
What I receive to convert (project file):
This project file has one worksheet that I am interested in.
On this worksheet, I only want information from Columns A through F.
There are headings for colums in row 7 with data starting with row 8.
Headings:
A7:SECTION
B7
ESCRIPTIOND7:RESPONSIBILITY
E7:COST
F7:SUB COST
Data below headings:
Section column(Column A):
Every row is either blank, contains a five digit number, contains a five
digit number with an apostrophe at the beginning, or contains "NO SPEC".
Description column(Column B-C):
This column of data is made up of columns B & C. Every row contains a blank
or text. Column B is the text description of the number section listed in
column A. Rows within Column C will have blanks or text as well. When text is
listed in column C, it is considered a sub category of the text listed in
column B.
Responsibility Column(Column D):
Contains blanks or text of the name of the assigned party.
Cost column(Column E):
Contains a number (dollar value), blanks, or text.
Subcost column(Column F):
Contains a number (dollar value), blanks, or text.
Information I do not want:
There is also general information describing the project in rows 1-5 that I
am not interested in.
There is also additional information starting in column G through N I don't
want.
There are also formulas in cells at the bottom of this data for summary(the
row this summary data starts in varies project to project but this
particular project starts in row 888 but another project could start in row
2000), but I am not interested in this summary data.
What I hope to achieve if possible:
A template file that creates budgets from any project file I receive.
I would like to be able to copy the worksheet from the project file into the
blank budget file where the macro will pull only the information I am
interested in from the project worksheet and puts it on another worksheet in
the format listed below.
Budget file columns:
Columns:
Account Code:
This colum only needs data from the section column. The section number will
be the account code, any blanks shoul be ignored, and the No Specs should be
included if assigned in value from the cost or subcost column.
Account description:
This should relate to the account code column with text describing it. This
data should come from the description column in column B of the project file.
Responsibilty:
This should correlate with the section number and description. It should
come from the responsibility column from the project file.
Labor:
This column should be a dollar value for all the labor or installation. Its
data comes from cost or subcost of the project sheet. Anywhere there is a
text "labor" or "installation" found in the project description column, it
should pull the value from cost or sub cost ccolumn and place in this budget
material column.
Material:
This column should be a dollar value for all the material. Its data comes
from cost or subcost of the project sheet. Anywhere there is a text
"material" or "materials" found in the project description column, it should
pull the value from cost or sub cost ccolumn and place in this budget
material column.
Subcontract:
This column should be a dollar value for all subcontracts. It should pull
any remaining values from the subcost project column. It value should have
its own row with corresponding data in the previous columns.
Other:
This column should be a dollar value of all remaining cost from the in the
project cost column. There should also be corresponding data in previous
columns.
Total:
This columns should just sum up the values of all previous columns listed.
This is the last column I need.
Additional data - I would like to sum up the total column at the bottom of
the data. Also, put a border around all of the data and bold border around
the column headings.
Although I can record macros and open VB, I cannot write code from scratch,
especially to accomplish this task. This is the best solution to what I am
trying to accomplish that I could come up with, any other suggestions are
welcome.
Please let me know if you need any additional information.
Thanks in advance for reading my dilemma and any help is greatly appreciated.