VBA Code: Copying rows from multiple sheets

Closed
NcTukek Posts 2 Registration date Wednesday October 10, 2012 Status Member Last seen October 11, 2012 - Oct 10, 2012 at 01:11 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 11, 2012 at 11:16 AM
Hello,
I'm trying to use VBA to copy rows that satisfy a certain 'If-Then' statement from multiple sheets in Excel onto one sheet. All of the sheets have the same columns and each set is one month in a year. I've gotten the code to work for one sheet but I need the next month's rows to be copied on to the final sheet relative to where the previous month's ended. The first month I've copied is 'April' and I am copying all of them onto a sheet titled 'Change Summary 1'. Here is my code thus far:

Option Explicit
Sub Module1()
Dim i
Dim e
Dim k
Dim l
Dim m
Dim n
Dim o
Dim p
Dim s
Dim t
Dim u
Dim v
Dim w
Set i = Sheets("April")
Set k = Sheets("May")
Set l = Sheets("June")
Set m = Sheets("July")
Set n = Sheets("Aug")
Set o = Sheets("Sep")
Set p = Sheets("Oct")
Set s = Sheets("Nov")
Set t = Sheets("Dec")
Set u = Sheets("Jan")
Set v = Sheets("Feb")
Set w = Sheets("Mar")
Set e = Sheets("Change Summary 1")
Dim d
Dim j
Dim a
d = 5
j = 5
a=5+d
Do Until IsEmpty(i.Range("I" & j))

If i.Range("I" & j) = "Yes" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
If k.Range("I" & j) = "Yes" Then
a = a + 1
e.Rows(a).Value = k.Rows(j).Value
End If
End If
j = j + 1
Loop


End Sub

My biggest problem is making the next month show up relative to the month before it on the 'Change Summary 1' sheet. Any suggestions are appreciated.


Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 10, 2012 at 07:33 PM
it seems to me you want to loop thru each sheet
yet in code you have

            If i.Range("I" & j) = "Yes" Then
                d = d + 1
                e.Rows(d).Value = i.Rows(j).Value
                If k.Range("I" & j) = "Yes" Then
                    a = a + 1
                    e.Rows(a).Value = k.Rows(j).Value
                End If
            End If 


but then I = april and k = may
so while u are looking at rows of sheet i, u still are refering to sheet may and yet you seem to say that i want to loop thru each sheet.
0
NcTukek Posts 2 Registration date Wednesday October 10, 2012 Status Member Last seen October 11, 2012
Oct 11, 2012 at 06:49 AM
Okay, how would I go about fixing that? This is the first piece of VBA code I've ever written so I'm kind of clueless at the moment.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 11, 2012 at 11:16 AM
So is this is what you want to do.
Loop thru each sheet
Loop thru each row
if value in J column is "YES", copy the row to summary sheet.

Are you sure you want to copy the row, as I dont know what if row 1 in two sheets has yes, where the data goes. It seems to me that there is only certain column you want to copy from each sheet to summary if the value of cell in sheet being looked at is yes.
0