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]

Re: PLease Help - VBA Copy Paste

I am also looking at an alternative to retrieving data from my control sheets (Labour, Equipment, Materials, Crews, Material Conversion, Factors) and using in the EstSheet instead of excel index function and excel formulas in the specific cells.

Ultimately, I don't want to see any formulas within the EstSheet cells...use vba to do all calculations and retrieve data.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

I uploaded a new version of workbook to dropbox with an additional sheet called "Task"

The "Task" sheet is the template I would like to add and link to the sheet as needed and numbered to identify the sheet it came from...in proper sequence on the Tab.​

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

Anyway of automating that code so I don't have to use a CommandButton_Click?

 

[mumps]

Re: PLease Help - VBA Copy Paste

By "sum all the values up to K3" do you mean you want the total of all K2 cells in all the linked sheets? This means that all the K2 cells would have the same number.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

By "sum all the values up to K3" do you mean you want the total of all K2 cells in all the linked sheets? This means that all the K2 cells would have the same number.

Sorry meant K2...

I want to add K2 values from all the linked sheets into the Main sheet.

Example...Sheet 1 K2...is actually the sum of =K9+K12+K20+K25+K29 in that sheet. Sheet is like a master sheet.

Create additional sheets linked to Sheet 1 K2 = (

K9+K12+K20+K25+K29 (Sheet 1) + (Sheet 1.01 K2)+(Sheet 1.02 K2)+(Sheet 1.03) etc.


​

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

The code you gave me that works...

Maybe a better way of looking at this is...

The code Pulls data from Sheet 1 K5 and adds all other sheets associated with Sheet 1...
ie = Sheet 1 K5 + (Sheet 1.01 K2)+(Sheet 1.02 K2)etc.
Returns the total value to "Data" Column G3
Sheet 2 K5 + (Sheet 2.01 K2)+(Sheet 2.02 K2)etc. returns value to G4

Then also do this same function to collect the data from in the same code...
(Sheet 1 K9) + (Sheet 1.01 K6) + (Sheet 1.02 K6) + (Sheet 1.03 K6) etc return to (Data Column K3) (column name Labour)

(Sheet 1 K12) + (Sheet 1.01 K9) + (Sheet 1.02 K9) + (Sheet 1.03 K9) etc return to (Data Column L3)

(column name Equipment)​

_{^{<strike>
</strike>}}(Sheet 1 K20) + (Sheet 1.01 K17) + (Sheet 1.02 K17) + (Sheet 1.03 K17) etc return to (Data Column M3)

(column name Materials)​

_{^{<strike>
</strike>}}​

_{^{<strike></strike>}}

(Sheet 1 K25) + (Sheet 1.01 K22) + (Sheet 1.02 K22) + (Sheet 1.03 K22) etc return to (Data Column N3)

(column name Subcontract)​

_{^{<strike>
</strike>}}​

_{^{<strike></strike>}}

(Sheet 1 K29) + (Sheet 1.01 K26) + (Sheet 1.02 K26) + (Sheet 1.03 K26) etc return to (Data Column O3)

(column name Plug)​

​

_{^{<strike></strike>}}​

 

[mumps]

Re: PLease Help - VBA Copy Paste

I think I've solved the request from Post #15 . I'll have a look at your request from Post #15 as soon as I can and get back to you. My time is a little tight right now.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

Really appreciate you help...I am new to vba and I really want to understand it, I have a ton of ideas I want to accomplish.

Thank you

 

[mumps]

Re: PLease Help - VBA Copy Paste

Let's take this one step at a time. Click here to download your file. On the "Data" sheet click the "Build Worksheets" button. Select sheet "1" and click the "Add New Tasks" button and see if does what you want. Is K2 in sheet "1" updated properly? I'm also a little confused because sometimes you refer to K2 and sometimes to K9. Do you want the same kind of update to happen with cells K9, K12, K20, K25 and K29 on sheet "1"?

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

I Just want to say I appreciate your help.

K2 is not updating properly...sorry.

The format I have this worksheet setup is obviously confusing and I don't want that as there will be a couple other people that may use it.

I have thought about the workbook and I think it should be approached a little different. Instead of 1 template (EstSheet)...I have created 2 templates...EstSheet & TskSheet.

EstSheet will be created from "Data" sheet with "Click to Build Worksheets"...as it does now. Just a different template format.
TskSheet will be created from the EstSheet's that are created...this will happen with the click of "Click to Add New Tasks" button on the newly created TskSheet's. I would also like to have a "Message Box" same as the message box that pops up with the "Click to Build Worksheets" button...Instead of Worksheets it has "Task's)

The EstSheets ie: 1,2,3 are a summary sheet for all TskSheets associated with it ie: 1 (2), 1(3), 1 (4) etc and likewise for 2 (2), 2 (3), 2 (4) etc.

I have put a description of the values that are needed to be copied/calculated/pasted into Sheet 1, 2, 3 etc (EstSheet template). See the template EstSheet...should make sense.

I have put a description of the values that are suppose to be copied/calculated/pasted into Sheet "Data" from all Sheets 1,2,3 etc (TskSheet template). See the "Data" sheet...should make sense.

Sorry for the change...but I thought if it was confusing to you, it would be confusing to others. This format I believe will be much easier code and very user friendly.

Link to revised workbook here https://www.dropbox.com/s/0ma1t0somfbmyns/Buildsheet.xlsm?dl=0 .

Hope I am not asking too much.
Brent