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

 

[mumps]

My time is really tight today. I will look at it as soon as I can and get back to you.

 

[bhalbach]

My time is really tight today. I will look at it as soon as I can and get back to you.

I can't thank you enough for your help...greatly appreciated.

 

[mumps]

Following your suggestion of adding both sheets, I managed to get it to work. You will notice that the button form the EstSheet is gone. To add additional sheets, you will have to click the button on the TskSheet. Click here for your file. If the creation of the sheets works for you, we will have to finalize how you want the sheets populated with data.

 

[bhalbach]

Following your suggestion of adding both sheets, I managed to get it to work. You will notice that the button form the EstSheet is gone. To add additional sheets, you will have to click the button on the TskSheet. Click here for your file. If the creation of the sheets works for you, we will have to finalize how you want the sheets populated with data.

Yah...That is what I was thinking. Nice work mumps

For data population please see the attached file, see Sheet "II.1"


https://www.dropbox.com/s/s606yu6is82esl5/bhalbach.xlsm?dl=0


I have input some formulas into the yellow cells that add get data from sheet' II.1(2), II.1(3), II.1(4), II.1(5)

This what should happen with Sheet II.1(EstSheet)

Then this would repeat for II.2

If that makes sense. ​

 

[mumps]

OK. I think we are making progress. Click here for the file.
After you create the first 2 sheets for each Bid Item, you will see an "Update" button on each copy of the EstSheet (for example II.1) and it will be populated according to the formulas. After you add an additional sheet when you click the "Click to Add New tasks" button and enter the data in the new sheet, you go back to sheet II.1 and click the "Update button and it will be updated with the data from the additional sheet. As you add sheets, you have to go back and "Update". Does this work for you?

 

[bhalbach]

OK. I think we are making progress. Click here for the file.
After you create the first 2 sheets for each Bid Item, you will see an "Update" button on each copy of the EstSheet (for example II.1) and it will be populated according to the formulas. After you add an additional sheet when you click the "Click to Add New tasks" button and enter the data in the new sheet, you go back to sheet II.1 and click the "Update button and it will be updated with the data from the additional sheet. As you add sheets, you have to go back and "Update". Does this work for you?

Awesome...you are a Rockstar.

This code language is more complicate than I thought.

The "Update" button worries me...The button could potentially need pressing 50, 100 times if I have that many EstSheets. Very high risk of this getting missed. Can it be automated?

I have put some formulas in the "Data" sheet in the yellow colored cells...This is where everything comes together.

I really appreciate your help...This would have taken me a year to figure out.

Did I mention you Rock...Thank you, Thank you, Thank you.

https://www.dropbox.com/s/rqobvmn001n0o7e/bhalbach.xlsm?dl=0

 

[bhalbach]

Awesome...you are a Rockstar.

This code language is more complicate than I thought.

The "Update" button worries me...The button could potentially need pressing 50, 100 times if I have that many EstSheets. Very high risk of this getting missed. Can it be automated?

I have put some formulas in the "Data" sheet in the yellow colored cells...This is where everything comes together.

I really appreciate your help...This would have taken me a year to figure out.

Did I mention you Rock...Thank you, Thank you, Thank you.

https://www.dropbox.com/s/rqobvmn001n0o7e/bhalbach.xlsm?dl=0

Ooops...

Had a formula mistake on "Data" sheet in column 'P' row 3 down...should be =($P$2/($G$2/G3))

 

[mumps]

I've tried several approaches to eliminate the need for an "Update" button but I can't come up with any working solution. Click here for your file. I have modified the formulas on the "Data" sheet. Click the button to add the new sheets. When you click the "Update" button on the EstSheets, they will be updated and so will the "Data" sheet. I think that this is the best I can do. You only have to click the "Update" button on the EstSheets once, after you have finished adding all the sheets. You don't have to click it every time a sheet is added. If it helps, I could add a warning to the user to remind them to click the "Update" button on all the EstSheets before they are allowed to save the file or close it. If you think this would help, please let me know and I will add it to the workbook.

 

[bhalbach]

Thank you mumps...

I have not had a good chance to see how the changes work yet, I will let you know.

I really appreciate all your help.

Couple questions...
- can I move anything around on the sheets or will this require modifying to code to accommodate ie: delete rows or columns that are not being used or moving row/cells that populate the EstSheet or Data sheet?

- from what you have seen how this workbook looks and works, any suggestions to maintain the same size working area? I would like each worksheet to look relatively the same, just not positive how I will approach this...trying to avoid scrolling.

- Could I use a Form to accomplish what I am doing and alleviate the scrolling/consistent sizing.

- any way to automatically size the worksheets to screen size? I use a laptop and also a large screen. I had the workbook up on my big screen today and it was a pain resizing to fit the different screens.

Thanks

 

[mumps]

Because the macros use cell references, moving or deleting rows or columns is not a good idea because that would change the references and the macros won't work. You can change the screen size using the Zoom Bar in the lower right hand corner. If you decrease the percentage, more data will be visible. I forgot to mention that now if you enter a value in column A of the "Data" sheet and exit the cell, a new row with the formulas will be inserted.