Creating a worksheet from others (IF statement)

[Guest]

Hi Guys,

I tried looking fort his answer and I think it is answered in parts of other
questions but I cant get it together.

Scenario:

I have a file called invoices.xls which is a workbook of 12 monthly
worksheets for the year. Each item in a monthly worksheet is a row that
houses project information financials etc (project cost, outsourced cost,
project name etc)

We also have another file (wip.xls) where we double handle all of that
information and cut and paste only those files that we are currently working
on.

I would like wip.xls to be automatically populated with certain cells from
invoice.xls

I would like to add a column into invoice.xls called 'WIP' and IF the cell
contains a 'Y' (meaning we are currently working on it) it appears in the WIP
document, If it does not contain a 'Y' then it is not included in the WIP
document. (Even better if I put in a checkbox)

I have no idea how to do this. The other thing is, can I then go and add
extra cells in each row in the WIP file without causing a problem. I would
like to open WIP and add a column which holds what people are currently
working on it, but I dont have this data in invoice.xls, nor do I want it in
invoice.xls.

Any help would be appreciated.

McKean

 

[Guest]

McKean,

I'm pretty sure that you can do this, whenever you open wip.xls, you can
set it to update from invoice.xls.

Below is some code which should work

I'm going to assume that your data has no blank lines in amongst the data
set, and that the column with the "y"s is column J (ie) the 10th column.
Just make adjustments as necessary

you can then call this macro in the "workbook_open" event of wip.xls

sub UpdateWip()
dim mySheet as worksheet
dim counter as integer
dim i as integer

Range("a2", Cells(Range("a1").CurrentRegion.Rows.Count, 9)).Delete
' this clears the data in wip.xls except for the first row
workbooks.open("C:\MyFolder\invoice.xls")
'obviously adjust this so that it points to the correct file
counter = 2
'this just will start the paste in wip.xls in row 2

for each mySheet in Worksheets
mySheet.select
for i = 1 to Range("a1").currentRegion.rows.count
if cells(i,10).value = "Y" then
Range(cells(i,1),cells(i,9)).copy
Windows("wip.xls").activate
Cells(counter,1).pastespecial paste:=xlValues
application.cutcopymode = false
Windows("invoice.xls").activate
counter = counter + 1
end if
next i
next mySheet
end sub

I have tested this to some extent. I hope that it helps you. I haven't used
a checkbox, you can always adjust the code to handle that instead of a "Y".

Katie

 

[Guest]

Hi Katie,

Thanks for this, but at the risk of you possibly having wasted your time
here, I have decided that the problem is far more complicated that I last
thought and may not even be possible.

I cant have the sheet automatically update because I will lose data that I
have added from the last time.

To explain:

On the very first instance of opening the spreadsheet (call it week 1) I
would like it to populate, as you have replied to earlier. I would then add
in a column of what the current progress is for this week (this column does
no exist in invoice.xls) I would print it and present it at a meeting.

The next time I open the spread sheet, if any rows that had a 'y' in them
previously have since had that 'y' removed, I would like that row deleted.

Any rows that had a 'y' and still have a 'y' I would like to stay as they are

Any row that didn't have a 'y' (or didnt exist) and now has a 'y' I would
like added to the wip document.

I think this will cause a world of pain to even the best expert. Maybe I
should go back to double handling

McKean