• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking issue

Hi Mark ,

An additional check can be included within the code , so that if a particular worksheet does not have any data , it is skipped in the rollup.

Please confirm first that the macro does what you want it to do.

Narayan
 
Somendra Misra said:
Do repeat pt 1 in my above comment. Its working at my end.


Regards,
Click to expand...
Works perfect now. Thnak you. DO you mind if I ask addtionjal question should I encounter any additonl issues as I contoinue to decvlop this modle?

Thank you again.

Mark in Plano, TX (20 degrees here now - yikes!)
NARAYANK991 said:
Hi Mark ,

An additional check can be included within the code , so that if a particular worksheet does not have any data , it is skipped in the rollup.

Please confirm first that the macro does what you want it to do.

Narayan
Click to expand...

Thank you.

I feel like a total idiot now :(

After my initial post, I got so hung-up on explaining and re-explaining my issue that I created a basically invalid sample file, although I think most of the functionality from a formula and macro perspectvie will still work (but I stil need assistance as macro code isn't my thing - I can read and edit, but writing from scratch - not for me).

Attached is an almost replica file of the actual file that we use. As I referenced in one of my initial posts, the actual tabs are form-like. The employee enters some data at the top (PART 1), clicks Yes, No, or NA for the fixed error questions (PART 2). For any Yes responses, the employee would enter the Error number and then enter in the text explaining the actual error (PART 3). There will never be more that 12 errors. My intent is still to have one record per employee tab and rolled up similar to the file that you sent last. So, if you would please take a look at attached file and advise what action(s) will be required to achieve the same purpose, that is for the records on the rollup tab to appear contiguous, even if the other data repetas, to show the number of rows that represet the number of actual errors on the tab. I sure appreciate your extra effrot to help get me a solution. Again, I apologize for the miscommunication.

Mark
 

Attachments

  • Actual_Rollup_Format.xlsx
    19.5 KB · Views: 1
cyberx1 said:
Works perfect now. Thnak you. DO you mind if I ask addtionjal question should I encounter any additonl issues as I contoinue to decvlop this modle?

Thank you again.

Mark in Plano, TX (20 degrees here now - yikes!)


Please disregard the Works perfect now - I mistakenly sent unedited and before review of my actual file which I hadn't looked in a couple of days prior to today.
Click to expand...
 
Hi Mark ,

In your file , the tab labelled Sample1 has a header for the error responses section , with the result the actual data starts from cell A31. In the other two sheets Sample2 and Sample3 , there are no headers , with the result the actual data starts from cell A28.

Can this be standardized ?

Narayan
 
NARAYANK991 said:
Hi Mark ,

In your file , the tab labelled Sample1 has a header for the error responses section , with the result the actual data starts from cell A31. In the other two sheets Sample2 and Sample3 , there are no headers , with the result the actual data starts from cell A28.

Can this be standardized ?

Narayan
Click to expand...
Ooppss...revised attached.

Mark
 

Attachments

  • Actual_Rollup_Format.xlsx
    20 KB · Views: 1
Hi Mark ,

See if this is OK.

Please remember that each time you run the macro , it will go through all the sheets , and overwrite what ever was there earlier on the Rollup tab.

Narayan
 

Attachments

  • Actual_Rollup_Format.xlsm
    27 KB · Views: 5
NARAYANK991 said:
Hi Mark ,

In your file , the tab labelled Sample1 has a header for the error responses section , with the result the actual data starts from cell A31. In the other two sheets Sample2 and Sample3 , there are no headers , with the result the actual data starts from cell A28.

Can this be standardized ?

Narayan
Click to expand...
Oh, just FYI that in PART 2, Yes means OK, No means Error found and documented in PART 3, and N/A just means N/A.

Mark
 
Hi Mark ,

The point I was making was that in the code , we are placing the cursor in cell A42 , and going up till we find the first non-blank row. If this row number is greater than 30 , it is assumed that there is some error data , if not , there is no error data ; is this logic OK ?

Narayan
 
NARAYANK991 said:
Hi Mark ,

See if this is OK.

Please remember that each time you run the macro , it will go through all the sheets , and overwrite what ever was there earlier on the Rollup tab.

Narayan
Click to expand...
Excellent.

After reviewing your code, guess I wil just have to edit in the event of new column names, correct?

Can functionality be put back on Rollup tab to repeat data cells so all records show as contiguous?

Does Match formula in cell K34 on Sample1 tab necessary?

Thank you again.

Mark
 
Hi Mark ,

Sorry , that is unnecessary. Please delete it.

If you add new columns of data , changes will be needed in the copy statement or in the assignment statements or in both. I think you will not have any problems in modifying the code to suit.

Narayan
 
Hi, cibyerx1!

Surely a bit late but just in case give a look at the uploaded file. This is the code:
Code: 
Option Explicit

Sub SomethingAnythingEverything()
    ' constants
    Const ksSummaryWS = "Rollup"
    Const ksSummaryRange = "ErrorTable"
    Const ksChildTopRight = "D2"
    ' declarations
    Dim rngS As Range, rngC As Range
    Dim I As Integer, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        With Worksheets(J)
            If .Name <> ksSummaryWS Then
                If .Range(ksChildTopRight).Value <> "" Then
                    Set rngC = Range(.Range(ksChildTopRight), _
                        .Range(ksChildTopRight).End(xlDown).End(xlDown).End(xlUp).End(xlToLeft).End(xlToLeft))
                    rngC.Copy
                    rngS.Cells(I + 1, 1).PasteSpecial xlPasteValues
                    I = I + rngC.Rows.Count
                End If
            End If
        End With
    Next J
    ' end
    Application.CutCopyMode = False
    rngS.Cells(2, 1).Select
    Set rngC = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!
 

Attachments

  • Linking issue - Rollup (for cyberx1 at chandoo.org).xlsm
    20.5 KB · Views: 1
SirJB7 said:
Hi, cibyerx1!

Surely a bit late but just in case give a look at the uploaded file. This is the code:
Code: 
Option Explicit
 
Sub SomethingAnythingEverything()
    ' constants
    Const ksSummaryWS = "Rollup"
    Const ksSummaryRange = "ErrorTable"
    Const ksChildTopRight = "D2"
    ' declarations
    Dim rngS As Range, rngC As Range
    Dim I As Integer, J As Integer
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
    ' process
    For J = 1 To Worksheets.Count
        With Worksheets(J)
            If .Name <> ksSummaryWS Then
                If .Range(ksChildTopRight).Value <> "" Then
                    Set rngC = Range(.Range(ksChildTopRight), _
                        .Range(ksChildTopRight).End(xlDown).End(xlDown).End(xlUp).End(xlToLeft).End(xlToLeft))
                    rngC.Copy
                    rngS.Cells(I + 1, 1).PasteSpecial xlPasteValues
                    I = I + rngC.Rows.Count
                End If
            End If
        End With
    Next J
    ' end
    Application.CutCopyMode = False
    rngS.Cells(2, 1).Select
    Set rngC = Nothing
    Set rngS = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!
Click to expand...
Thank you very kindly, but I can't use your solution. Reason is code. I don't understand code nor will I be able to maintain the code given changes to the format and things like that. Guess I was looking for a function solution, whcih I do underdstand. I'm going to use some of your tips from previous file uploads, but I think for the sake of finally putting this matter to rest, I'm just going to take the hard way and link all 12 cells from each tab times 80 tabs giving me 960 rows on the rollup tab, format with F5 to fill in the blank cells, sort, and then pivot. Thnak you for your input. In the future, I'll be more clear of my issue and clearly explain that I'm not seeking a code soluiton.

Take care.
 
cyberx1 said:
Thank you very kindly, but I can't use your solution. Reason is code. I don't understand code nor will I be able to maintain the code given changes to the format and things like that. Guess I was looking for a function solution, whcih I do underdstand. I'm going to use some of your tips from previous file uploads, but I think for the sake of finally putting this matter to rest, I'm just going to take the hard way and link all 12 cells from each tab times 80 tabs giving me 960 rows on the rollup tab, format with F5 to fill in the blank cells, sort, and then pivot. Thnak you for your input. In the future, I'll be more clear of my issue and clearly explain that I'm not seeking a code soluiton.

Take care.
Click to expand...
After some quick trials, even the F5 function isn't gogin to help me with my problem - I'm just screwed at this point:(
 
Hi, cyberx1!

Let me try to explain the code:

Code: 
    ' constants
    Const ksSummaryWS = "Rollup"
    Const ksSummaryRange = "ErrorTable"
    Const ksChildTopRight = "D2"
Here you set the summary worksheet name, the name of the dynamic range on it, and the rightmost cell of data.

Code: 
    ' declarations
    Dim rngS As Range, rngC As Range
    Dim I As Integer, J As Integer
Here you declare the variables you're gonna use.
rngS, range summary
rngC, range for each child (non summary worksheet)

Code: 
    ' start
    Set rngS = Worksheets(ksSummaryWS).Range(ksSummaryRange)
    With rngS
        If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    End With
    I = 1
Here's the initializing part of the code. You actually define the summary range variable, and if it contains previous data, then you clear it.

Code: 
    ' process
    For J = 1 To Worksheets.Count
        With Worksheets(J)
            If .Name <> ksSummaryWS Then
                If .Range(ksChildTopRight).Value <> "" Then
                    Set rngC = Range(.Range(ksChildTopRight), _
                        .Range(ksChildTopRight).End(xlDown).End(xlDown).End(xlUp).End(xlToLeft).End(xlToLeft))
                    rngC.Copy
                    rngS.Cells(I + 1, 1).PasteSpecial xlPasteValues
                    I = I + rngC.Rows.Count
                End If
            End If
        End With
    Next J
Here's where all happens. You:
- set line count for summary as 1 (I)
- go thru all the worksheets, excluding summary, and if rightmost top cell (D2) isn't empty (i.e., there's valid data in the tab) then you:
.. define the child range (rngC)
.. copy it
.. paste values at the next available row in summary
.. update line count

Code: 
    ' end
    Application.CutCopyMode = False
    rngS.Cells(2, 1).Select
    Set rngC = Nothing
    Set rngS = Nothing
    Beep
Here's the final housekeeping. You reset copy selection, select positioning cell, destroy range variables used and say "beep".

And that's all. Hope it helps.

Regards!
 
You must log in or register to reply here.
Back
Top