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]

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.

I have noticed the add line feature on Data sheet...awesome. I noticed that when I add to column A on Data and hit tab, enter or click in another cell, it defaults to and selects columns F through to P on the next row below the added row. Could it default to the next column in the same line which would be column B?

Also noticed the Control buttons on "Data" became huge for some reason. I have not been able to repeat it yet, not sure if it was related to my large screen. Are the control button sizes relative to the screen size when zooming etc?

When clicking "Click to Add New Task" on the TskSheet I believe it is copying the sheet that is active...can the specific cells be cleared when hitting the "Click to Add New Task" or use the TskSheet template which will have all the necessary cells empty?
​

Thanks again for all you help, it has been priceless...

 

[mumps]

I'm not sure why the buttons are changing in size. Which specific cells do you want to clear when clicking "Click to Add New Task"? Do you want a reminder to the user to click the "Update" buttons before closing or saving the file?

 

[bhalbach]

I'm not sure why the buttons are changing in size. Which specific cells do you want to clear when clicking "Click to Add New Task"? Do you want a reminder to the user to click the "Update" buttons before closing or saving the file?

Mumps,

Yes a reminder would be helpful to click the "Update" buttons...

The cells that would need to be cleared when "click to Add New Sheet) on TskSheet are:

The cells that I would need are:
B2, I2, C4, H5, C19. G19
RANGE C12:H14
RANGE C16:J17
RANGE C20:H22
RANGE C24:J26
RANGE C28:J30

Could I also ask for a couple other things?
-Change my Command Buttons to the same format as your "update" button...aesthetics.
-Another button on the EstSheet & TskSheet that would take me back to "Data" sheet...same format as your "Update"
-When click on column A or C on Data, could take me directly to that EstSheet.
-I noticed that after I have added the values into Column A of Data and new lines are created...if I come back later and try adding more into Column A of Data it does not add more lines. ​

Everything else seems to be working as it should.

 

[bhalbach]

Mumps,

Yes a reminder would be helpful to click the "Update" buttons...

The cells that would need to be cleared when "click to Add New Sheet) on TskSheet are:

The cells that I would need are:
B2, I2, C4, H5, C19. G19
RANGE C12:H14
RANGE C16:J17
RANGE C20:H22
RANGE C24:J26
RANGE C28:J30

Could I also ask for a couple other things?
-Change my Command Buttons to the same format as your "update" button...aesthetics.
-Another button on the EstSheet & TskSheet that would take me back to "Data" sheet...same format as your "Update"
-When click on column A or C on Data, could take me directly to that EstSheet.
-I noticed that after I have added the values into Column A of Data and new lines are created...if I come back later and try adding more into Column A of Data it does not add more lines. ​

Everything else seems to be working as it should.

It would be great if the values that populates from "Data" to EstSheet also populate TskSheet. The from and to are below...

Data A1:TskSheet B1

Data B1:TskSheet C1

Data C1:TskSheet I1

Data D1:TskSheet J1​

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

 

[mumps]

Here your file.
When I tried adding Bid Items to column A in the "Data" sheet, it added a new line every time.
I forgot to mention that you don't have to "Reply With Quote" each time, you can simply click "Reply".

 

[bhalbach]

Can I change my drop down list boxes into Combo Boxes that will search while typing rather than be restricted to only scrolling a list? My lists are getting large, and will continue to grow.

 

[bhalbach]

Mumps
When I download the file it is trying to reference a file elsewhere for info ...this never happened before.

 

[mumps]

Click here. I have made a change in your drop down list in column B. In your previous list you had a bunch of spaces and duplicate entries. In your "Items" sheet, I have added a list of your selections in column Z and named the range as "Items". I used that named range in the data validation. If you add or delete a selection in column Z, your drop down list will automatically update. Also, if you double click any cell in column A or column C in the "Data" sheet, you will be taken to that EstSheet.

 

[bhalbach]

Mumps,

This is really working as I had envisioned...thank you so much.

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

I have added another element which I have accomplished some of the code, but am struggling with a couple things.
Can you help with this as well?

Hope this description makes sense.


New template called "progress".
-I have added another control button on the "Data" sheet called "Build Progress".
-This control button creates a new worksheet from the template "Progress" and populates it from the "Data" sheet.
-I need the new copied progress template to be called "Progress (1)...I have not managed to get code to do this.
-Value from "Data" B1 to populate "Progress (1)" C1...I have not managed to get code to do this.
-Values from "Data" column A populate "Progress" starting at B5 and proceed to automatic add lines in the "Progress(1) to match the "Data" lines in column A (adding new lines same as you made the Data sheet work)
.......I have the first row of values from "Data" populating A5, B5, E5, F5, H5 with "Build Progress" code.
This is part one of my "Progress" element...



Part 2 is to "Add Progress" from the newly created "Progress(1)...(just like the "Add Tasks" code).
-This sheet will need to be named "Progress(2)" and so on.
-This will create an exact copy, with values from Column I5 and down getting copied to J5 and down...the I5 and down values need cleared.
-Likewise for I11 and down getting copied to J11 and down...the I11 and down values need cleared.
​

 

[mumps]

I need more specific details to explain the following:

Values from "Data" column A populate "Progress" starting at B5 and proceed to automatic add lines in the "Progress(1) to match the "Data" lines in column A

and

values from Column I5 and down getting copied to J5 and down...the I5 and down values need cleared. -Likewise for I11 and down getting copied to J11 and down...the I11 and down values need cleared.

Add a few more Items in column A of the "Data" sheet to give me a better idea and then upload a copy of the file with what "Progress(1)" and "Progress(2)" should look like.