VBA Copy Paste

[bhalbach]

I really need some assistance if someone could help...likely really easy for you guys.

I have been searching for examples I could use for 2 days...

I am trying to use VBA to copy data from multiple worksheets within a workbook to a worksheet called "Data" within the workbook.
- copy multiple data from sheets in the workbook, excluding some sheets that may or may not be hidded.
- the data comes from various cells in the sheets and needs to go to specific columns on the "Data" sheet
- the "data" sheet has the names of the sheets/tabs already in it starting at A3...the sheets/tabs were created from the "Data" sheet using VBA and I would like to validate the data coming from each sheet to the names in column A
- avoiding the screen flicker would be nice
- Can this be accomplished automatically without a command button? Like live data, as I work in the workbook.

Please help.

Brent

 

[bhalbach]

File can be found here...https://www.dropbox.com/s/1k2sdmmll6yw00w/bhalbach-progress.xlsm?dl=0

 

[mumps]

It would be easier for me to make the changes and test the macro if I could see what your revisions look like. Could you upload a copy of the file?

 

[bhalbach]

File can be found her... https://www.dropbox.com/s/9vjj36q0cyr39v4/bhalbach-progress.xlsm?dl=0

I have shanged the layout of both the EstSheet & TskSheet for userfriendly easy to read.

Problem is I cannot get the Estsheet to "Update" will all the TskSheets.

Thus will also affect the "Data" sheet updating...

 

[bhalbach]

I am also still having the Bid Item issue with the characters I put in "Data Column A"

I have been testing where the bid item # is C.1, C.2, C.3, D.1, D,2, D.3 etc...errors when I press Build Worksheets.

 

[mumps]

Click here.

 

[bhalbach]

mumps,

All seems to be working, until I required a modification.

I added 3 additional columns for necessary info to "Data"...which are now the A,B,C columns. I have managed to change all necessary code references on EstSheet and TskSheet.

However, I am struggling to follow the code for "Build Progress" and "Add Progress"...the 3 rows I inserted on "Data" has affected the code references.

One thing I discovered when I was testing was if there is a Duplicate value in column D(Bid Item column) when I use the "Update" on the EstSheet, the updating values from EstSheet will populate the Data sheet (columns I through T) with the same values for the duplicate "Bid Item in Column D". Is there a way around this, as this can cause me serious problem if a duplicate is used in column D of Data. I do come across duplicates often. In the attached file there were numerous times that there were duplicates and I had to rename the "Bid Item" to everything work...and I may still have a duplicate.

And somehow I messed up the double click on Data sheet to shortcut to each EstSheet...was operating on columns A & C until I inserted 3 rows...Columns are D & F now.

Could you assist me please.

https://www.dropbox.com/s/6p1t0ghuax2r09u/bhalbach_progress.xlsm?dl=0​

 

[mumps]

The "BuildWorksheets" macro is designed not to create duplicate worksheets. So if you have duplicates in column D of "Data", only the first occurrence of the duplicate value will be used to create a sheet. You will not have an EstSheet for any of the other duplicates. I don't know how you would want to handle this duplicate situation. When you say "the updating values from EstSheet will populate the Data sheet (columns I through T)", the "UpdateEstSheet" macro should not be populating the "Data" sheet with anything. It updates only the EstSheet where the button is clicked.

 

[bhalbach]

Well maybe there was not a EstSheet for the duplicates, I didn’t check to see. How does the Data sheet populate? I thought it was happening in conjunction with the “Update EstSheet”

I will just have to be diligent on duplicates in column D, should be fine. Not sure how else to deal with it.

Could you please help me out with the “double Click” on Data sheet, and the revision to the code on “Build Progress” and “Add Progress”.

 

[mumps]

Click here.
Give this a try. I'm not sure if I got everything because it's hard to keep track of all the changes. I forgot to mention that the reason why I designed the macro to not add an EstSheet for any of the duplicate values is because Excel doesn't all allow more than one sheet with the same name. It will generate an error. So if you must have duplicate values, you will have to manually modify each duplicate name slightly if you want an EstSheet to be created. Also, when you design a worksheet, avoid using merged cells because they almost always create problems for Excel macros. By the way, look for the little 'house' icon.

 

[bhalbach]

So I have tried the revision.

When the Progress is created from the command "Build Progress" on Data, it does create a Progress.
- The descriptions that are being copied from Data column E, is overwriting the formats on "Progress". Losing the borders on the Progress sheet.
- When I try to " Add Progress" all seems to work except the "Extra Work" section of the Progress page. (This is the 2nd Table on the Progress sheet.) The quantities are not copying over, column "N" should copy to column "M" and column "L" should have it's values cleared.
- The "Extra Work" section (This is the 2nd Table on the Progress sheet.) should allow for additional rows to be added automatically if there is a change in column "D" at the bottom of the table.
- The "Progress(1), Progress(2) should populate "D2" on Progress Sheet...Columns A,B,C may not be always be visible.
- Can you make the newly created "Progress" sheets/tabs appear at the left, instead of at the end/Right?

I finally got to try the little "House", Can the descriptions also populate the Index Page? Love this page.

Thanks again.​