PDA

View Full Version : [SOLVED] Copy Rows and Paste Values



LordDragon
07-28-2015, 06:58 PM
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
07-28-2015, 11:21 PM
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
07-29-2015, 02:37 PM
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
07-29-2015, 08:46 PM
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
07-30-2015, 06:45 PM
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
07-31-2015, 12:01 AM
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.