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]

mumps...
I have added some data as well as some notes in cells explaining what is intended. File here https://www.dropbox.com/s/or7n05s77c0ewgg/bhalbach.xlsm?dl=0

Let me know if this makes sense once you have read the notes in the workbooks...thanks again.

 

[mumps]

I'm sorry for the delay. I'm having some problems with my computer. The error you are getting when you try to "Update" sheet "C" is caused by the macro looking for sheet names that are 'Like' the active sheet name. The active sheet name in this case is "C" so the macro accepts the sheet "Crew" as being 'Like' the sheet "C". The value in K2 in the "Crew" sheet is blank so an error is generated. This problem will not go away if you use alpha characters to name the Items in column A of the "Data" sheet. Before you were using numbers or "II" and since there are no sheets that have numbers in their name or "II" in their name, the error is not generated. What naming convention will you use to name the Items in column A?

 

[bhalbach]

The naming convention changes dependent on what a customer uses in their specifications.

It could be aby of the following...
Numeric
Alpha Numeric
Roman Numerals.
Combination of any of these.

If I changed the names of sheets such as the "Crew" to something different would that help?

 

[mumps]

I just solved that problem by having the macro look at the active sheet's name plus the left bracket:

If ws.Name <> ActiveSheet.Name And ws.Name Like ActiveSheet.Name & "(*" Then

I'm working on the rest of the stuff. I'll get back to you a soon as I can.

 

[mumps]

Click here. Give it a try and see how it works for you.

 

[bhalbach]

It error when I click "Build Progress" on the "data" sheet.

Run-time error '1004':
That name is already taken. Try a different one.


If I delete the 2 sample Progress (1) and Progress (2) sheets that were in the workbook I get the following.
Run-time error '1004':
To do this, all merged cells need to be the same size.


If I unmerge on "Progress" cells in rows 'B' and 'C' that are merged It will run without error.
However, it does not populate properly on the Progress template.
-populates above row 4, inserting rows, on the "Progress"template and uses the formats of row 4. Should be populating below row 4 on "Progress"
-only some of the values from data are populating from "Data"

If you run the "Build Progress" button you will see what I mean.

​

 

[mumps]

Let's try this one and see how far we got. Click here.

 

[bhalbach]

Nice work...

Hope this makes sense...

I gave you wrong info...I had columns wrong!

-When click "Add Progress" on Progress(1) sheet
....values from column "K" are copied. (Not "J" as I had said)
....copied column "K" values are pasted to column "J" in new "Progress(2) sheet.

-values being cleared are on wrong "Progress" sheet
....Progress(1) data is to remain intact when click "Add Progress" and a new Progress(2) is created.
....Progress(1) column "K" values are copied to Progress(2) column "J"
....on the new Progress(2) column "I" values are cleared (not Progress(1) column I, in this case)

-cell borders are being copied with values from "Data"
-copied values should match destination sheet formats.

-only the first 2 rows of values from "Data" are populating fully onto new "Progress(1)" when click "Build Progress" on "Data"
....the 3rd, 4th, etc rows of values from "Data" only copies and populates column "A" & "B" on Progress(1)
....columns "C, D, E" from "Data" are not coping over to "Progress(1) columns "E, F, H"

I am still testing some scenarios, but this is what I have seen thus far.

Thank you.

 

[mumps]

Click here.

 

[bhalbach]

Seems to be working great, nice work mumps.

I have been trying to follow your code as best I can understand to learn vba, but it is difficult. What you have done, (which was probably very simple stuff for you), I’ve wanted to accomplish for a long time.

One thing I did notice is that if the templates (TskSheet, EstSheet & Progress) are hidden the buttons do not work correctly, I believe it is overwriting the template and also the sheets that are crated are hidden as well.

Would a “Lock Sheet” macro be easy to put on the sheets? I was planning on formatting cells to be locked where I do not want anyone to accidentally type, but this would not lock and preserve the values in the unlocked cells.

I can’t say enough thanks for the time you have spent on this, I really appreciate it.