Solved

# Creation of New Worksheet Not working

Posted on 2011-03-14
345 Views
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
wb1.Sheets(1).Activate
ActiveSheet.Paste

'~~> Change the file Name Here

'Call fitWidth

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
``````
0
Question by:BBlu
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 42

Expert Comment

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

Rory Archibald earned 500 total points
ID: 35136092
Try using:
``````rng.copy
``````
``````Sheets("QB_Expenses").Range(rng.Address).Copy
``````
and see if that helps.
0

Author Comment

ID: 35143184
Perfect Rorya!  Thanks!
0

Author Closing Comment

ID: 35143185
Perfect Rorya!  Thanks!
0

Author Comment

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

rng.copy

and this one didn't:

0

LVL 85

Expert Comment

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

Question has a verified solution.

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

### Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…
###### Suggested Courses
Course of the Month4 days, 2 hours left to enroll