VB Code help on Worksheets

[VNouBA]

Something is not adding up to my Sheet4.

I would copy and paste all codes but could take a long time.

Sheet1 = main entry (Some files dont get to go on Sheet2 until complete information has been inserted and that is on Column E. Once this is complete then the user will go to the next sheet...

Sheet2 = Time frame, all code in here are almost perfect but doesnt copy well on Sheet4

Sheet3 = Short process but necessary (Only column C is transfered) all code in here are almost perfect but doesnt copy well on Sheet4

If you play a little with the sheets you will see the process... but to keep in mind that each files are different from on another... Some file can take up to 1 month to process and never move from sheet1. This is why it's not adding up to my sheets4 and 5.

[VNouBA]

Details of what is required from each sheets are comming soon.

Omg if someone finds this problem... I will freak out of joy.

[VNouBA]

Sheet1 is the main entry. All project received by the user will be imputed in this Sheet.
The user will need to insert the title the date he received the file and so on.
The user cannot continue the process until column E is completed.

This is the best explanation:

The user will receive a file. A number is attached to it. A date and an amount.
The user will then insert the information on the first line. In sheet1 the first cell is A5.
Example used is, project name “Testing”

Sheet1
A5 = Project number (“Testing”)
B5 = Date received (“2012-05-17)
C5 = Amount of the file (“100$”)
D5 = Relative information (Drop down menu with options)
E5 = Name of the person that will do the review. (Drop down menu with reviewer names)

A5 will be transferred to all sheets, except sheet6 as this is only the archives.
Also
A5, B5, C5 D5 and E5 will be transferred to sheet5 on the row of the project number.

Sheet2
You will see A5 is already populated from Sheet1’s A5.
Therefore on the same row the user will add more information that will be transferred to Sheet5 only… Same for Sheet3 and sheet4.

The problem I am having is the following:

If 4 projects is pending in Sheet1… Sheet4 will not respond to the next project

If project 5 is inserted in sheet1 then the user continues on sheet2 then sheet3 on sheet 4 that user will be able to do the data entry but will transfer on sheet5 on the next available cell / or row and this is where it gets complicated.

So info on Sheet1 will be the following

A5 = Project1
A6 = Project2
A7 = Project3
A8 = Project4
A9 = Project5

Sheet4 will be the following

A5 = Project1
A6 = Project2
A7 = Project3
A8 = Project4
A9 = Project5 (user is ready to add the contracts therefore B9 = 004) the VBA code will do it’s functions.

BUT

If the user goes and continue Project2 from Sheet1 the information will not go in the right cells vs that project.

This Workbook is a little complicated but only need tweeking on some codes. Most of the functions works kinda good.

[VNouBA]

Let me know if I anyone needs more explanation.

[VNouBA]

Any thoughts if I would need to go to a Consultation with this project?


I mean most of the codes are present but kind of out of my league to tweek it to the right commands.

[Tinbendr]

This seems to be working.

[VNouBA]

You guys are awsome

Thank you for also fixing my codes. Less traffic and clean.

[VNouBA]

In the same workbook, is there a way to:

If a project started and ended on sheet2 so it's pending. The user will have another project and will be ready to continue, surpases sheet2 all the way the sheet5 (talking about the process).

Is there a way for the information to look-up "A" (project number) and to insert the info on the same line of that project number?

Ex:

if you add one project Sheet1 and then go in sheet2. But stop there. The project number will be in A5.

If you add a second project in Sheet1 then you insert the full process till you get to sheet5.

Lets say that the user continues project1. it will go in the next available cell in Project2 instead of project1.

Could this be fixed?

Plus the copy and paste in Sheet4 has a bug and only copies and paste I5 and J5 even if I select row 8

[VNouBA]

I got the Sheet4 fixed:

[VBA]
If (Target.Address = "$I$" & TCol Or Target.Address = "$J$" & TCol) And Target.Value <> "" Then
Range("I" & TCol & ":J" & TCol).Copy
If Sheets("Report").Range("N" & TCol).Value = "" Then
Sheets("Report").Range("N" & TCol).PasteSpecial xlPasteValues
Else
Sheets("Report").Cells(Rows.Count, "N").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
End If
[/VBA]

[Tinbendr]

Is there a way for the information to look-up "A" (project number) and to insert the info on the same line of that project number?

When I only need a short lookup, i use the worksheetfunction function.

Since you only need the row number;
[vba]Sub test()
MsgBox Application.WorksheetFunction.Match(Range("A5"), Worksheets("MERX").Range("A5:A30"))
End Sub
[/vba] This will return the row # on Merx using Sheet1!A5 as the lookup. Of course, you'll have to pass the Column A value dynamically. E.g. Range ("A" & TCol) (And don't forget to add 5 since you start on row 5.)

I'm still not following the process all that well. Give it a shot. If you can't get the code to work, post again and give detailed processes for me and I'll try to help.

... Sheet4 has a bug and only copies and paste I5 and J5 even if I select row 8

I missed that one!

[VNouBA]

I will explain the process as detailed as possible. By the way thank you so much forthe time you have helped me.

The workbook will be use by users to input information on all of the files that arein their possession.

There are four steps in this process.
Not all processes are at the same time.
The userwill be given a file to work on. When all is done, the user will then post this file on a system for a minimum of 40 calendar days.
When the 40 days are up the file is brought back for a review and to issue contracts.
This file can have multiple contracts.
Then a processfor delivery is watched until the user obtains all delivery invoices to then close the file.
The user will get about one file a week. Not all the files have the same timeframe. Some could take up to one year to be posted on the system, some in less than a day.

As an example, I have over 50 files, each with maybe 4 or more contracts attached to this same file.

I will give you the process of one file

We have 4 processes to the report. Each file is its own process.
Step one isthe pre-requisition stage. The user will build the file based on the request.So therefore this is why I have the files name, Date, amount, and the strategy.I also have a comment for each file if there is pending issues with the file.No files are the same. So each file will have a different name.
Step two is the posting on the system. The posting is a minimum of 40 days but could beextended depending if the users are asked for an amendment on this file. In the MERX sheet, I have added a section that the user will be able to test when itshould come out (40 days) then decide if they want this particular date. A postingcan never land on a weekend or holiday even. Then the user will have a view ofwhen the file will close from this system and also the file full date limit (90days).

Step threeis the evaluation period. The user will evaluate the file once it has returnand construct contracts based on how many winning companies. Each company willhave the same file number but different codes at the end. You will see insheet4 when you add a number in column B, it will add the file name and the /00?/HSfor the extensions. All must be linked to this same file.
Step fouris all the maintenance of the file and the contracts links to the file. Each contractare different but always belonging to the main file.
I have areport of when the user will have complete a delivery status, he will then addit to the last page, “Archives”. This will be for records only.
The managerwill be able to go in each user’s workbook and view the status of each filesfor his report. I have saved and locked each workbook with a password and themanager has a excel with hyperlink that he can view all his staff workload.

Now here is the tricky part. The function of the workbook will be the following. Everysheets starts at row5

Sheet1: (Iwill take example file “Project1” as a file name)
The userwill type in “Project1” in A5”. This will copy itself to each sheet, so sheet2,3, 4 and 5. This will help the user identify where the file stand. And it’slife cycle process.

Only ColumnA will be copied to each sheets. The other cells in that row will be copied tothe report section. But all informationfrom the other cells must be linked with “Project1”.

Extra: I amtrying to figure out a code that will transfer my comments to sheet5’s commentcell vs “Project1”.

This is thehard part to explain. Each sheet has acomment column. So if the user adds acomment to sheet1 it will be transferred to sheet5’s comment. Always staying onthe same row of “Project1”.

If the useradds a comment in Sheet2, it will copy and adds itself to Sheet5’s comment butafter the comment of sheet1. So on and so on. But always in a flow startingfrom sheet1 to sheet4.

Example:
Sheet1= Row(Project1) Column (Thecomment)= Hello
Sheet2= Row(Project1) Column (Thecomment)=How are you
Sheet3= Row(Project1) Column (Thecomment)=Fine you
Sheet4=Row(Project1) Column (Thecomment)=Good
Sheet5= Row(Project1) Column (Thecomment)=Hello. How are you. Fine you. Good.



Not all projects have the sametimeline. Some project could take month even year to start. Some will take onlyone day.

But all projects must be its own rowand must be able to stay its own row.

If there is 10 projects in Sheet1 that are pending and 5 on contracts, when you continue one that is pending itneeds to be always link to his file number.

[Tinbendr]

OK, I'm to the testing stage, but I've run out of time.

Give it a whirl and report.

[VNouBA]

Not at my workstation but will make more explanation tomorrow.

I cannot thank you enough for helping me.

So far so good but I don't quiet understand the Column A (requisition number) vs Column F (Comments).

Will report back tomorrow

[VNouBA]

I will explain the functionality of one project life cycle. I use one project and Row 5 as an example. (Sheet2 will be posted shortly)


Sheet1

A5 = File number; Needs to be copied and paste (permanently) to Sheet2, 3, 4 and 5 on the next available row. This could also be done on the “yes” function at column G. Therefore if “yes” is selected, then copy and paste (permanently) to Sheet2, 3, 4 and 5 on the next available row.

*The look for next available row should start looking in row5 then look if there is a next available row.

So column A would be copied to Sheet2, 3, 4 and 5 on selecting “yes” from the drop down menu.

B5 = The date of the file received; Needs to be copied to sheet5 on the same row of the file number. B5=Sheet5 B5

C5 = Value; Needs to be copied to sheet5 on the same row of the file number. C5 =Sheet5 C5.

D5 = Information; Needs to be copied to sheet5 on the same row of the file number. D5 =Sheet5 D5.

E5 = Information; Needs to be copied to sheet5 on the same row of the file number. E5 =Sheet5 E5.

F5 = Comments; Needs to be copied to sheet5 on the same row of the file number. D5 =Sheet5 P5.

G5 = If “yes” is selected then clear content; or; copy A5 Sheet2, 3, 4 and 5 on the next available row *The look for next available row should start looking in row5 then look if there is a next available row.

[VNouBA]

Sheet2

A5 = Equals the information transferred from Sheet1. (Already transferred)

B5; C5; D5 = Calculation only this information will not be transferred.

E5; F5; G5 = Date; Needs to be copied to sheet5 on the same row of the file number. E5, F5, G5 will be copied to F5, G5, H5.

H5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. H5 =Sheet5 P5 but after the information from sheet1’s comments.


Column I will clear the content from that row.

[VNouBA]

Sheet3

A5 = Equals the information transferred from Sheet1. (Already transferred)

B5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 I5

C5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. C5 = Sheet5 P5 but after the information from sheet1 and sheet2’s comments.



Sheet4 is complicated and longer to explain therefore could take up more time to explain.

[VNouBA]

Sheet4 Functionality Example of one contract only.

A5 = Equals the information transferred from Sheet1. (Alreadytransferred)

B5 = Number of contracts; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 J5.

C5 = Contracts number; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 K5

D5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 K5

H5 = Number of contracts; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 M5.

I5 = Contracts number; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 N5

J5 = Information; Needs to be copied to sheet5 on the same row of the file number. B5 = Sheet5 L5

M5 = Comments; Needs to be copied to sheet5 on the same row of the file number in the comments. M5 = Sheet5 P5 but after the information from sheet1, sheet2 and sheet3’s comments.

K5 and L5 will be incorporated in later stage.

Column N will clear the content from that row.

[VNouBA]

Question:

Would it be easier if Column A would be copied to sheet5 only but also transferred to sheet2 first?

Example:

Sheet1
A5 = If column G is selected "yes" then Copy to sheet2 and Sheet5's next available row.

Sheet2 = If column I is selected "yes" then Copy A5 to sheet3 next available row.

Sheet3 = If column D is selected "yes" then Copy A5 to sheet4 next available row.

[VNouBA]

Would this make sense for Sheet1?


[VBA]
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count = 1 Then ' stops the code looping
If LCase(Target.Value) = "yes" Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 1)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Cells(Target.Row, 1), Cells(Target.Row, 5)).Copy
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).ClearContents
End If
End If
End If
[/VBA]


**Sheet2 would be more complicated cause it would need to lookup sheet5's file number information before being transferred.

[VNouBA]

Should have more code today.
Trying a new approach.