Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Report

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Please see attached. I would like the report depicted on sheet2 be generated via VBA on sheet4. Any ideas?
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798571' date='17-Oct-2009 13:37']Please see attached. I would like the report depicted on sheet2 be generated via VBA on sheet4. Any ideas?
    Thanks in advance.[/quote]
    Doers the attached Autofilter approach suit your needs?
    Attached Files Attached Files
    Regards
    Don

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798577' date='17-Oct-2009 14:27']Doers the attached Autofilter approach suit your needs?[/quote]

    Thanks. Not sure; how did you generate columns A through C?

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798582' date='17-Oct-2009 15:38']Thanks. Not sure; how did you generate columns A through C?[/quote]

    Columns A through C are constant (not affected by the macro). Rows with the result 0 or less are hidden. This filter is re-applied each time Sheet 4 is activated.
    Regards
    Don

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798583' date='17-Oct-2009 14:50']Columns A through C are constant (not affected by the macro). Rows with the result 0 or less are hidden. This filter is re-applied each time Sheet 4 is activated.[/quote]

    Thanks. However, when I erase the contents of sheet4 and try an run the macro again, I get a "Run-time error'1004' Autofilter method of Range class failed" message...

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798587' date='17-Oct-2009 16:41']Thanks. However, when I erase the contents of sheet4 and try an run the macro again, I get a "Run-time error'1004' Autofilter method of Range class failed" message...[/quote]

    Do not erase Sheet4; it will update to the latest changes in Sheet1.
    Regards
    Don

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798593' date='17-Oct-2009 16:00']Do not erase Sheet4; it will update to the latest changes in Sheet1.[/quote]

    What will happen when I first install the macro into my file? Will it run, or give me an error?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798595' date='17-Oct-2009 17:03']What will happen when I first install the macro into my file? Will it run, or give me an error?[/quote]

    IF you have Sheet4 set up to return the data from Sheet1 in the same fashion as I have done.

    And if the apparently blank cells in column E contain space characters.

    And if you copy across both the Module1.Macro1 and Sheet4,Worksheet_Activate procedures.

    Then the macro should run automatically each time you activate Sheet4.

    I suspect that you have not yet removed the filter from column E to realize that all of Sheet1 is captured in Sheet4; Activating Sheet4 fires Macro1 which filters out rows with 0 in column E.
    Regards
    Don

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798606' date='17-Oct-2009 17:29']IF you have Sheet4 set up to return the data from Sheet1 in the same fashion as I have done.

    And if the apparently blank cells in column E contain space characters.

    And if you copy across both the Module1.Macro1 and Sheet4,Worksheet_Activate procedures.

    Then the macro should run automatically each time you activate Sheet4.

    I suspect that you have not yet removed the filter from column E to realize that all of Sheet1 is captured in Sheet4; Activating Sheet4 fires Macro1 which filters out rows with 0 in column E.[/quote]

    OK, here is what I want to accomplish: I start out with a blank sheet4. When I run your macro, I want it to do what I showed in my original posting. It is impossible to "set up" a sheet 4 before the fact. I am not following what you have said.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798611' date='17-Oct-2009 18:46']OK, here is what I want to accomplish: I start out with a blank sheet4. When I run your macro, I want it to do what I showed in my original posting. It is impossible to "set up" a sheet 4 before the fact. I am not following what you have said.[/quote]

    Try the following code.
    Code:
     Option Explicit
     Public Sub Create_Report()
     Dim FR As Long 'First Row
     Dim LR As Long 'Last Row
     Dim FC As Long 'First Col
     Dim LC As Long 'Last Col
     Dim ContSet As Long
     Dim TgtRow As Long
     Dim TgtCol As Long
     Dim TgtType As Long
     
    	 Sheets("Sheet1").Activate
    	 FR = Cells(1, 1).End(xlDown).Row
    	 FC = Cells(1, 1).End(xlToRight).Column
    	 LR = Cells(Rows.Count, 1).End(xlUp).Row
    	 LC = Cells(FR - 1, Columns.Count).End(xlToLeft).Column
    	 
    	 With Sheets("Sheet4")
    		 .Cells.Clear
    		 For TgtRow = 1 To (LC - FC + 1) * (LR - FR + 3)
    			 ContSet = Int(TgtRow / (LR - FR + 3)) + 1
    			 If TgtRow / (LR - FR + 3) = ContSet - 1 Then _
    				 ContSet = ContSet - 1
    			 TgtType = TgtRow Mod (LR - FR + 3)
    			 Select Case TgtType
    			 Case 0
    				 'Leave row blank
    			 Case 1
    				 .Cells(TgtRow, 1) = Cells(1, FC - 1 + ContSet)
    				 .Cells(TgtRow, 1).Font.Underline = xlUnderlineStyleSingle
    			 Case Else
    				 For TgtCol = 1 To FC - 1
    					 .Cells(TgtRow, TgtCol) = Cells(TgtType, TgtCol)
    				 Next TgtCol
    				 .Cells(TgtRow, 5) = Cells(TgtType, FC - 1 + ContSet)
    			 End Select
    		 Next TgtRow
    		 
    		 For TgtRow = (LC - FC + 1) * (LR - FR + 3) To 1 Step -1
    			 If .Cells(TgtRow, 5) = 0 And .Cells(TgtRow, FC - 1) <> "" Then
    				 .Cells(TgtRow, 1).EntireRow.Delete
    			 End If
    		 Next TgtRow
    	 End With
     End Sub
    Regards
    Don

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Don, the code appears to do the trick. Can I have, say, 100+ items and say 75+ contracts?

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798681' date='18-Oct-2009 17:08']Thanks Don, the code appears to do the trick. Can I have, say, 100+ items and say 75+ contracts?[/quote]

    Yes.
    Regards
    Don

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798705' date='18-Oct-2009 19:19']Yes.[/quote]
    Don, a slight change. the location of my data is actually as follows: Old column A is no column AC, column B, AD, column C, AE, and old columns D:H now AG:CC, with other (irrelevant) data in columns A:AB and CE:CF. How would I change the code to allow for this?
    Thanks again for your help.

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='jlkirk' post='798747' date='19-Oct-2009 07:21']Don, a slight change. the location of my data is actually as follows: Old column A is no column AC, column B, AD, column C, AE, and old columns D:H now AG:CC, with other (irrelevant) data in columns A:AB and CE:CF. How would I change the code to allow for this?
    Thanks again for your help.[/quote]
    Can you guarantee that Row 1 of the first column to the right of the table (CD1) will be empty?
    Regards
    Don

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='798757' date='19-Oct-2009 07:22']Can you guarantee that Row 1 of the first column to the right of the table (CD1) will be empty?[/quote]

    Yes. Does it matter as well if there is other data located below that which we are manipulating?

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •