Solved

Creation of New Worksheet Not working

Posted on 2011-03-14
6
328 Views
Last Modified: 2012-08-14
My man Sid helped me figure out how to create the ability to click on a cell and see the details behind the total expenses for a particular department/month combination via the creation of a new worksheet.  The sample list I gave him, however, only had one year.  My real data goes back several years, so I added another line to match the year, as well.  And for some reason, the new code produces only a partial list.  I've banged my head against the wall a little bit and can't figure out why the loop is not going through and returning the entire list.  I'm hoping someone can see the error in the code without my having to recreate the file, removing the sensitive data, so I can post it.
Sub CommandButton1_Click()
    Dim i As Long, lastRow As Long
    Dim rRow As Long, cCol As Long
    Dim mMonth As Long
    Dim yYear As Long
    Dim rng As Range
    Dim wb1 As Workbook
    Dim Dept As String

    '~~> Change the range to the relevant range here
    If Intersect(Selection, Range("r8:u13")) Is Nothing Then
        'the intersect function checks to see if the selection exists in the given range
        MsgBox "Please select the cell in the correct range"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
   'Sheets("QB_Expenses").Activate
    lastRow = Sheets("QB_Expenses").Range("A" & Rows.Count).End(xlUp).Row
    
    ' finds last row in QB_Expenses Worksheet
     
   'This part tells the macro which dept/month combination we are working with
   rRow = Selection.Row
 
    cCol = Selection.Column
 
    mMonth = Month(Cells(7, cCol).Value)
    'What month are we looking for?
   
 yYear = Year(Cells(7, cCol).Value)
    'The month columns are all in row 7
    
Dept = Cells(rRow, 14).Value
    'What Dept are we looking for
    'The Departments rows are all in column 14
    
    Set rng = Sheets("QB_Expenses").Range("A1:G1")
    'On Error GoTo ErrorCatch
    
    For i = 2 To lastRow
        If Month(Sheets("QB_Expenses").Range("C" & i).Value) = mMonth _
        And _
        Year(Sheets("QB_Expenses").Range("C" & i).Value) = yYear _
        And _
        Sheets("QB_Expenses").Range("D" & i).Value = Dept Then
                Set rng = Union(rng, Sheets("QB_Expenses").Range("A" & i & ":G" & i))
           'if the month, year, and dept match, add this row to the open workbook
           
        End If
    Next i
    Sheets("QB_Expenses").Range(rng.Address).Copy
     Set wb1 = Workbooks.Add
     wb1.Sheets(1).Activate
    ActiveSheet.Paste
    
    '~~> Change the file Name Here
    'wb1.SaveAs "\\adpsfs\Accounting\Bobby\MyFile.xls"
 
'Call fitWidth

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Open in new window

0
Comment
Question by:BBlu
  • 3
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35135498
It appears you are looking for yYear in the same row and column as mMonth.  Could this be your problem?  Would not yYear be in a different column?

Dave
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35136092
Try using:
rng.copy

Open in new window

instead of:
Sheets("QB_Expenses").Range(rng.Address).Copy

Open in new window

and see if that helps.
0
 

Author Comment

by:BBlu
ID: 35143184
Perfect Rorya!  Thanks!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Closing Comment

by:BBlu
ID: 35143185
Perfect Rorya!  Thanks!
0
 

Author Comment

by:BBlu
ID: 35144575
Actually, I meant to ask, why did this work:

rng.copy

and this one didn't:

Sheets("QB_Expenses").Range(rng.Address).Copy
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35145139
If you use Range with an address string you are limited as to how long that address can be (255 chars) so if you have lots of separate areas, you will end up missing some. More simply, you already have a range object so you may as well use it! :)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question