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]

Re: PLease Help - VBA Copy Paste

Can you post a screen shot of what your "Data" sheet and one of the other sheets look like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

I don't know what I am doing with these links to assist with...tried opening them and they only opened excel.

 

[mumps]

Re: PLease Help - VBA Copy Paste

The first link will bring you to a page in the Forum with instructions on how to post screen shots. The second two links will take you to sites where you can upload your files for free. Once you upload the file, you can mark it for "sharing" and you will be given a link to the file that you can post here.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

Not sure if I have done this correctly...should be an excel worksheet here.

https://www.dropbox.com/s/2w0pwz05ge12s2a/Buildsheet.xlsm?dl=0

 

[mumps]

Re: PLease Help - VBA Copy Paste

After looking at your file, I have a few questions. You mention that the "Data" sheet has sheet names starting in A3, however, column A contains "Bid Item" not sheet names. I do see some sheet names in row 1 starting at column K to column S. Could you please clarify. If you want data from the sheets to be copied to the "Data" sheet, you will have to be very detailed and specific as to which data from those sheets you want to copy and where on the "Data" sheet you want to paste that data. Use a few examples from your data referring to specific cells, rows, columns and sheets to describe what data you want transferred and to where.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

Next goto the first worksheet created, which will be named the value from Data A3, second sheet would be named the value from Data A4 etc. Then click the command button named "Click To Add New Tasks" which is CommandButton1....this inserts Range A4:O33 from EstSheet starting at A34. The more times the CommandButton1 is clicked the more times A4:O33 from EstSheet is inserted below the last insertion.

This will give you an idea of what is going on in the workbook.



What I need to do...

The data sheet is where I would start to use this worksheet.

"data" sheet column A starting at row 3 is where I would input a text/numbers/etc which could be text, numbers or combination including characters. When all data has been input starting at Column A row 3...I click CommandButton2 (Click to build worksheets) and it creates a new sheets (utilizing sheet "EstSheet" as a template) for ever line starting at A3 and down until there is no more lines with data in column A. I have a glitch however, if there is an exact duplicate in column A it creates a runtime error...I don't want duplicates but do want the warning, need to be capable of changing the data without error.

"data" sheet column B starting at row 3 is a list box that I choose a description from...Ideally a combo box that would filter as I type data into it, if the specific data does not exist adding it to the Items sheet would be awesome.
"data" sheet column C starting at row 3 is data input.
"data" sheet column D starting at row 3 is currently a data input, however it should be linked to whatever is in column C.
"data" sheet column E start at row 3 is a data input.
"data" sheet column F is a calculation formula, if I can I want this to be vba code as well.

"data" sheet column G is the first range I need to gather data and paste here, this values come from K3 of ALL sheet names (sheets created from "data" during CommandButton2_click column A row 3). These sheets are the only sheets I need data copied from K3 and they should have some kind of validation so they can match sheetname K3 to "data" Column A values.

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

I am not sure if this is possible as well...


CommandButton1_Click
Instead of doing what I am doing with CommandButton1... could I create sub/child worksheets that are linked to each of these worksheets (worksheets created from CommandButton2)? CommandButton1_Click creates as many EstSheet Range A4:O33 copies as I require and adds it to the existing sheet progressing down the sheet as I need...this could be anywhere from 0 to 20, 30, or more. This would be more user friendly.


Cell K2 of all the worksheets (created from CommandButton2) is a calculation summing values in the worksheet...I am using O5 of the worksheet to sum data with a formula because every time I complete a range copy with CommandButton2 I need to sum that data into K2 as well. Wish I could use VBA for this as well.

 

[mumps]

Re: PLease Help - VBA Copy Paste

This macro should copy all the K2 data to column G of the "Data" sheet and also perform the calculation in column F:

Sub GetSheetData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Sheets("Data").Range("A3:A" & LastRow)
        Sheets("Data").Range("G" & rng.Row) = Sheets(CStr(rng.Value)).Range("K2")
        Sheets("Data").Range("F" & rng.Row).Value = Sheets("Data").Range("C" & rng.Row).Value * Sheets("Data").Range("E" & rng.Row).Value
    Next rng
    Application.ScreenUpdating = True
End Sub

I'm not sure what you mean by

could I create sub/child worksheets that are linked to each of these worksheets

Do you mean that instead of adding Range A4:O33 to the bottom of the same sheet, you want to create separate sheets for each added range? If so, how would they be linked?
Also please explain what sum you want VBA to return in the K2 cells.
Do you want to eliminate the error you get when you create the sheets and there are duplicate values in column A of the "Data" sheet or do you just want a warning?

 

[bhalbach]

Re: PLease Help - VBA Copy Paste

This macro should copy all the K2 data to column G of the "Data" sheet and also perform the calculation in column F:

Sub GetSheetData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Sheets("Data").Range("A3:A" & LastRow)
        Sheets("Data").Range("G" & rng.Row) = Sheets(CStr(rng.Value)).Range("K2")
        Sheets("Data").Range("F" & rng.Row).Value = Sheets("Data").Range("C" & rng.Row).Value * Sheets("Data").Range("E" & rng.Row).Value
    Next rng
    Application.ScreenUpdating = True
End Sub

I'm not sure what you mean by Do you mean that instead of adding Range A4:O33 to the bottom of the same sheet, you want to create separate sheets for each added range? If so, how would they be linked?
Also please explain what sum you want VBA to return in the K2 cells.
Do you want to eliminate the error you get when you create the sheets and there are duplicate values in column A of the "Data" sheet or do you just want a warning?

Awesome...works great. Thank you

Yes instead of adding the range to bottom, could I have another template/sheet created that links to the sheet...for example sheet 1 created from the data sheet (CommandButton2) needs 5 or 6 or whatever additional sheets (CommandButton1) that would link to Sheet 1 and sum all the values up to K3...Auto name the added sheets something like .01...therefore 1.01, 1.02, etc to identify they link to sheet 1...or color coded...and be in proper order on the Tab sequence. So Tab would be Sheet 1, followed by 1.01, 1.02, then Sheet 2 followed by 2.01, 2.02 etc.