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]

You are very welcome.
If you want to prevent anyone from accidentally changing a cell, you would have to lock the cell and protect the sheet with or without a password. However, all your macros would have to unprotect the sheet at the beginning and then protect it again at the end, for example:

Sub x()
    Sheets("Sheet1").Unprotect
    'your code goes here
    Sheets("Sheet1").Protect
End Sub

 

[bhalbach]

What about the hidden sheets, it appears the templates (EstSheet,TskSheet and Progress) cannot be hidden to work correctly.

 

[mumps]

You would have to make the sheet visible at the beginning of the macro and then hidden at the end.

psht.Visible = True
'code
psht.Visible = False

 

[bhalbach]

Can you address the Hidden Sheets issue for when the templates (TskSheet, EstSheet, Progress) are hidden that the sheets that they create generate correctly.

I am also wondering about a table of contents or index to easily navigate around the workbook. Just like the got stuck to Data button and the double click on Data cells to go to the TskSheet.

Can there be a Index/table of contents on every sheet in the workbook somehow to navigate the workbook sheets very efficiently?

I have found some code to make a Table if Contents sheet which I think is great and I think I will try to do this. But I have not found anything for putting on every worksheet.

Any ideas?

 

[mumps]

Click here. I have modified a few of the macros to take care of the hidden sheets. If you look at the Quick Access Toolbar in the upper left, you will see a small icon of a house. If you click this button, a contents sheet will be created with hyperlinks to all the sheets in the workbook. If you add more sheets, they will be added when you click the button again. Please note that the hyperlinks will work only for the visible sheets.

 

[bhalbach]

Hi mumps,

I have tried the workbook and seems to be working good except one thing I see, which I do not know if it was there earlier.

On the sub "AddNewProgress()"

"I14" is being cleared out which is a header line of my table on "Progress"
"K14" is being copied and pasted into "J15"...K14 is header line of table on "Progress"

Looks like it is initializing the copy and clear in these columns, 1 line before it should...line 14, instead of line 15. I suspect this is because it is searching for the empty cell...this is happening when I have no values in the table populated.

And I cannot seem to find the icon of a house.​

 

[mumps]

Click here. The house icon should be on the menu at the very top to the right of other icons such as "Save" or "Close".

 

[bhalbach]

I have reformatted the EstSheet to be user friendly. This also involved the “UpdateEstSheet” having cells remapped in the code...should be simple but I cannot get it to update.

 

[mumps]

Please upload a copy of the file and explain the changes in detail.

 

[bhalbach]

Mumps,
This is in the Sub UpdateEstSheet.

Values from TskSheet needed to update EstSheet
1Total=H6
MAT=H14
Labour=H21
DL=H18
CL=(H21-H18)
Equip=H27
Truck=H36
RE=H54
HE=H60
SubC=H42
PC=H48

EstSheet Destinations
1Total=H4
Mat=H11
Labour=H16
DL=H14
CL=H15
Equip=H19
Truck=H20
RE=H21
HE=H22
SubC=H26
PC=H29