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]

Oh...and the double click shortcut on the Data sheet that will hyperlink to the corresponding EstSheet.

 

[bhalbach]

And for whatever reason the "Update EstSheet" button is not updating.

 

[mumps]

I think that the problem I'm having in getting this right is that I am working with EstSheet, Tsksheet and Progress sheets that don't have any data. Therefore it's difficult to test the macros to see if data is being copied and pasted in the right places. Can you upload a version of the file that contains data in all these sheets, a version that is giving you the problems you described? Maybe we can finally get this done!

 

[bhalbach]

https://www.dropbox.com/s/yron9myw9dkuqee/bhalbach_progress.xlsm?dl=0

For whatever reason the Update EstSheet is working, no concern here now.

I have inserted comments on the Data, Progress(1), Progress(2) sheets to show where the problems are.

Hope this clarifies.

Thanks,
Brent

 

[mumps]

Click here.
I've modified the formulas in row 2 of "Data" starting in column I to add up to row 1000. If you need more rows, just change the formulas to suit. I've also modified the code to add the Progress sheets to the left instead of right.

 

[bhalbach]

Mumps,

Just 1 think I have found.

When click "Build Progress" on Data...If there are no values in Columns "A,B,C" on "Data" the Build Progress will error.

Everything else seems to be working without any issues.

 

[bhalbach]

Another thing is I cannot protect sheets without error. I suspect because of copying and updating going on thought the workbook.

Can anything be done with this? My formulas are at risk otherwise.

 

[mumps]

To fix the first problem, replace this line of code in the BuildProgress macro:

fBlankRow = dSht.Range("A" & dSht.Rows.Count).End(xlUp).Row

with this line:

 fBlankRow = dSht.Range("D" & dSht.Rows.Count).End(xlUp).Row

Which sheets do you want to protect?

 

[bhalbach]

I was wanting to lock all cells that are not needed for data entry.

At a minimum I would like to protect EstSheet's, TskSheet's, Data, All Progress's, Indirects.

I have information & formulas in every sheet that I would like to protect ideally.

 

[mumps]

On the EstSheet, TskSheet and Progress blank templates, lock all the cells you want to protect and unlock those cells that you use for data entry. Do the same for the Data and Indirects sheets. Once you've done that, upload a copy of the file.