Copy Rows and Paste Values

[LordDragon]

Greetings & Salutations,

I am confused about why this code isn't working. I used the exact same code in another workbook and it worked fine.

What I'm trying to do: Scan each worksheet. If any cell in Column A says "Yes" then it is to copy the row from C to E and paste them into the Order Summary page from A to C.

What it's doing: Exactly what it is supposed to do, but copying all the rows from each sheet and pasting them like it is supposed to, but then repeating that for each sheet for the same number of sheets in the workbook.

(For example: I selected one item by placing a Yes in Column A on one sheet. I then activated the code and it copied the correct information from that sheet and pasted it on the Order Summary sheet. Then did it again 11 more times (the number of sheets not being ignored by the code.)

Here is the sample code in its entirety.

Sub PreviewOrder()
'Unhides the Order Summary sheet.
'Finds the parts and quantities needed and moves them to the Order Summary sheet.


    Application.ScreenUpdating = False
    
    'Declare Variables
    Dim wkSheet As Worksheet
    Dim lngRow As Long
    Dim intCol As Integer
    Dim strYes As String




    strYes = "Yes"


    'Declare which sheets to ignore.
    For Each wkSheet In Worksheets
    
    If wkSheet.Name = "Instructions" Then GoTo NextSheet
    If wkSheet.Name = "Project Info" Then GoTo NextSheet
    If wkSheet.Name = "Order Summary" Then GoTo NextSheet
    If wkSheet.Name = "RMS Order" Then GoTo NextSheet
    If wkSheet.Name = "Master DataList" Then GoTo NextSheet
    
    'Determine how many rows there are for each sheet
    lngRow = Range("A" & Rows.Count).End(xlUp).Row


    'Declare what to do with each sheet not ignored.
    For Each cell In Range("A3:A" & lngRow)
        If cell = strYes Then
        Range(Cells(cell.Row, "C"), Cells(cell.Row, "C")).Copy
        Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Range(Cells(cell.Row, "D"), Cells(cell.Row, "D")).Copy
        Sheets("Order Summary").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Range(Cells(cell.Row, "E"), Cells(cell.Row, "E")).Copy
        Sheets("Order Summary").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
        
    Next cell


NextSheet:
    Next wkSheet
    
    'Unhide the Order Summary sheet.
    With ActiveWorkbook
    
    .Worksheets("Order Summary").Visible = True
        
    End With




Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select
Application.Goto Sheets("Order Summary").Range("A1"), True






End Sub

Thanks.

[vcoolio]

Hello LordDragon,


Try and replace all the following lines of code:-

   Range(Cells(cell.Row, "C"), Cells(cell.Row, "C")).Copy 
                Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 
                Range(Cells(cell.Row, "D"), Cells(cell.Row, "D")).Copy 
                Sheets("Order Summary").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues 
                Range(Cells(cell.Row, "E"), Cells(cell.Row, "E")).Copy 
                Sheets("Order Summary").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

with this:-

Range(Cells(cell.Row,"C"),Cells(cell.Row,"E").Copy
Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial xlPasteValues

Also, it may be worth unhiding the "Order Summary" sheet first so move this part of the code:-

 With ActiveWorkbook 
         
        .Worksheets("Order Summary").Visible = True 
         
    End With

towards the top of the code just after this line:-

strYes = "Yes"

Also, you probably don't need to declare the string variable:-

Dim strYes As String
strYes = "Yes"

and just have the line:-

If cell = "Yes" then

intead of:-

If cell = strYes Then

(not that this should make a difference).

And, one more thing, it may be worth adding this:-

wkSheet.Select

Just above:-

lngRow = Range("A" & Rows.Count).End(xlUp).Row

If you have only selected certain sheets of the non-ignored ones to have the criteria "Yes" in Column A, then make sure that the remaining ones don't have "Yes" in Column A. Alternatively, leave all the "Yes" criteria in all the non-ignored sheets and add some code to clear the "Order Summary" sheet each time the code is executed. Doing this will basically update the "Order Summary" sheet.


Cheerio,
vcoolio.

[LordDragon]

vcoolio,

I tried your suggestion here.

with this:-

Range(Cells(cell.Row,"C"),Cells(cell.Row,"E").Copy
Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial xlPasteValues

I do like that this code is a lot cleaner and easier to read. However, it's doing the same thing the other code was doing and is still putting the same item on the list several times.

If you have only selected certain sheets of the non-ignored ones to have the criteria "Yes" in Column A, then make sure that the remaining ones don't have "Yes" in Column A. Alternatively, leave all the "Yes" criteria in all the non-ignored sheets and add some code to clear the "Order Summary" sheet each time the code is executed. Doing this will basically update the "Order Summary" sheet.

I have another code that clears the entire workbook and restores it to the original condition so it can be used again. I have however also taken your advice and added a code that will clear the Order Summary page if the user runs the PreviewOrder macro again. That was something I was considering anyway to help prevent an inaccurate order.

There are no other sheets that have a "Yes" in column A other than the ones that have parts to be ordered. My reset code sets all those cells to No, so there are no accidental parts ordered. Even the sheets that are ignored don't have a Yes in Column A, I just ignored them because there's no point in checking them in the first place.

I'm sure there is something simple that I'm missing that is causing the duplication of the parts ordered.

Here is the revised code:

Sub PreviewOrder(control As IRibbonControl)
'Unhides the Order Summary sheet.
'Finds the parts and quantities needed and moves them to the Order Summary sheet.


    Application.ScreenUpdating = False
    
    'Declare Variables
    Dim wkSheet As Worksheet
    Dim lngRow As Long
    Dim strYes As String




    strYes = "Yes"
    
    'Unhide the Order Summary sheet.
    With ActiveWorkbook
    .Worksheets("Order Summary").Visible = True
    End With


    'Clear the Order Summary sheet to make sure the order isn't accidentally duplicated.
    With ActiveWorkbook
    .Worksheets("Order Summary").Range("A2:A2000").ClearContents
    .Worksheets("Order Summary").Range("B2:B2000").ClearContents
    .Worksheets("Order Summary").Range("C2:C2000").ClearContents
    .Worksheets("Order Summary").Range("D2:D2000").ClearContents
    End With
    


    'Declare which sheets to ignore.
    For Each wkSheet In Worksheets
    
    If wkSheet.Name = "Instructions" Then GoTo NextSheet
    If wkSheet.Name = "Project Info" Then GoTo NextSheet
    If wkSheet.Name = "Order Summary" Then GoTo NextSheet
    If wkSheet.Name = "RMS Order" Then GoTo NextSheet
    If wkSheet.Name = "Master DataList" Then GoTo NextSheet
    
    'Determine how many rows there are for each sheet
    lngRow = Range("A" & Rows.Count).End(xlUp).Row


    'Declare what to do with each sheet not ignored.
    For Each cell In Range("A3:A" & lngRow)
        If cell = strYes Then
        Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
        Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'        Range(Cells(cell.Row, "D"), Cells(cell.Row, "D")).Copy
'        Sheets("Order Summary").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
'        Range(Cells(cell.Row, "E"), Cells(cell.Row, "E")).Copy
'        Sheets("Order Summary").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
        
    Next cell


NextSheet:
    Next wkSheet
    




Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.Goto Sheets("Order Summary").Range("A1"), True






End Sub

[vcoolio]

Hello Lord Dragon,

Try the following scaled down version of the code in a standard module (I've left out the "unhide" bit for now):-

Sub PreviewOrder()
     
Application.ScreenUpdating = False
    Dim wkSheet As Worksheet
    Dim lngRow As Long
     
Sheets("Order Summary").Range("A2:D2000").ClearContents
For Each wkSheet In Worksheets
    
     If wkSheet.Name = "Order Summary" Then GoTo NextSheet
     If wkSheet.Name = "Instructions" Then GoTo NextSheet
     If wkSheet.Name = "Project Info" Then GoTo NextSheet
     If wkSheet.Name = "RMS Order" Then GoTo NextSheet
     If wkSheet.Name = "Master DataList" Then GoTo NextSheet
        
wkSheet.Select
lngRow = Range("A" & Rows.Count).End(xlUp).Row
        
    For Each cell In Range("A2:A" & lngRow)
            If cell = "Yes" Then
                Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
                Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
Next cell
NextSheet:
Next wkSheet
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select
     
End Sub

I've attached my dummy work book for you to peruse with the above code in a standard module.
The search starts in A2 in each sheet in the dummy work book.

Let me know how it goes.

Cheerio,
vcoolio.

[LordDragon]

Ok. Figured it out.
Part of the project requirements was to keep the parts sheet for any system not being used hidden so the users aren't ordering parts they don't need. Also to keep the number of sheets smaller so it's easier to find what you want.

So on the Project Info sheet there are selection boxes for the user to indicate which systems they need parts for. If they select the system, the sheet is unhidden.

The code here, still checked those sheets to see if there was anything to move to the Order Preview sheet. Since they users wouldn't be ordering parts for those systems anyway, I added some code to tell it to just ignore the sheets that are hidden, as well as the ones that were listed before, as they are not normally hidden.

Since you can't select sheets that are hidden, I had omitted the "wkSheet.Select" code.

When I turned this off in the sample book you provided, it created the problem as in my workbook. The reason my code worked before in my Beta version of the workbook, is because the sheets were not hidden in that one, so the code existed.

Here is a copy of the finished, working code:

Sub PreviewOrder(control As IRibbonControl)
'Unhides the Order Summary sheet.
'Finds the parts and quantities needed and copies them to the Order Summary sheet.


    Application.ScreenUpdating = False
    
    'Declare Variables
    Dim wkSheet As Worksheet
    Dim lngRow As Long
    Dim strYes As String


    strYes = "Yes"
    
    'Unhide the Order Summary sheet.
    With ActiveWorkbook
    .Worksheets("Order Summary").Visible = True
    End With


    'Clear the Order Summary sheet to make sure the order isn't accidentally duplicated.
    With ActiveWorkbook
    .Worksheets("Order Summary").Range("A2:A2000").ClearContents
    .Worksheets("Order Summary").Range("B2:B2000").ClearContents
    .Worksheets("Order Summary").Range("C2:C2000").ClearContents
    .Worksheets("Order Summary").Range("D2:D2000").ClearContents
    End With


    'Declare which sheets to ignore.
    For Each wkSheet In Worksheets
    
    If wkSheet.Name = "Instructions" Then GoTo NextSheet
    If wkSheet.Name = "Project Info" Then GoTo NextSheet
    If wkSheet.Name = "Order Summary" Then GoTo NextSheet
    If wkSheet.Name = "RMS Order" Then GoTo NextSheet
    If wkSheet.Name = "Master DataList" Then GoTo NextSheet
    If wkSheet.Visible = False Then GoTo NextSheet
    
    wkSheet.Select
    
    'Determine how many rows there are for each sheet
    lngRow = Range("A" & Rows.Count).End(xlUp).Row


    'Declare what to do with each sheet not ignored.
    For Each cell In Range("A2:A" & lngRow)
        If cell = strYes Then
        Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
        Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
        
    Next cell


NextSheet:
    Next wkSheet
    
Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select


End Sub

Thanks for the help vcoolio. I figured it was something simple, but probably would not have figured it out as quickly without your help.

[vcoolio]

Hello Lord Dragon,

Most excellent!

That's amazing. At about 3.00am this morning, as I lay in bed, it dawned on me that hidden sheets could be the issue because the additional data had to be coming from somewhere. I was on the verge of getting up, logging on and sending you another post (Yes, I know. I need to get a life!) but I resisted and just thought it best to wait and see what your next reply would be. That's great that you sorted it out and I'm glad that I was able to help out somehow.

You may still want to scale down the code a little more, viz:-

Change this:-

 With ActiveWorkbook 
        .Worksheets("Order Summary").Range("A2:A2000").ClearContents 
        .Worksheets("Order Summary").Range("B2:B2000").ClearContents 
        .Worksheets("Order Summary").Range("C2:C2000").ClearContents 
        .Worksheets("Order Summary").Range("D2:D2000").ClearContents 
    End With

to this:-

Sheets("Order Summary").Range("A2:D2000").ClearContents

You won't need to use it as a "With" statement.

Also, change this:-

If wkSheet.Name = "Instructions" Then GoTo NextSheet 
        If wkSheet.Name = "Project Info" Then GoTo NextSheet 
        If wkSheet.Name = "Order Summary" Then GoTo NextSheet 
        If wkSheet.Name = "RMS Order" Then GoTo NextSheet 
        If wkSheet.Name = "Master DataList" Then GoTo NextSheet 
        If wkSheet.Visible = False Then GoTo NextSheet

to this:-

If wkSheet.Name = "Order Summary" _
     Or wkSheet.Name = "Instructions" _
     Or wkSheet.Name = "Project Info" _
     Or wkSheet.Name = "RMS Order" _
     Or wkSheet.Name = "Master DataList" _
     Or wkSheet.Visible = False Then GoTo NextSheet

I'll leave it to you.

Cheerio, Lord Dragon.
vcoolio.