Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with syntax for copy and pasting rows across sheets
Following is the code I am using to check for a value in sheet1 against sheet
2. If value not found in sheet2 then I want to copy the value from sheet1 and paste it at the bottom of the list of values in sheet2. What is not working is the statement that I am using to copy the row from sheet 1 and pasting it to sheet2 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) Following is the code Private Sub CommandButton2_Click() Dim r As Long 'Last row of data on Sheet 1 (row number) Dim c As Range 'Range/counter Dim Rng As Range 'Row 2 to row 'r' (above) as counter Dim Cel As Range 'Cell value to find from Sheet1 on Sht2 Dim Sht1 As Worksheet 'Sheet1 Dim Sht2 As Worksheet 'Sheet2 Dim Sht3 As Worksheet 'Sheet3 Set Sht1 = Sheets("sheet1") Set Sht2 = Sheets("sheet2") Set Sht3 = Sheets("sheet3") 'Get last row Sheet 1, Column a r = Sht1.Range("a65536").End(xlUp).Row 'Start with Sheet1 Sht1.Activate 'Set 'counter' as first row of data to last row of data (Col a) Set Rng = Range(Cells(2, 1), Cells(r, 1)) 'For cells first to last (counter) For Each Cel In Rng 'find first Sheet1.Cell.Value in sheet 2 With Sht2.Cells Set c = .Find(Cel, LookIn:=xlValues) If c < "" Then GoTo phred r = Sht2.Range("a65536").End(xlUp).Row + 1 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not working... End With phred: MsgBox "went to phred" Next End Sub any tips or recommendations is much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with syntax for copy and pasting rows across sheets
You should have gotten an error message that the Object Variable was not set
if the Find method returned nothing. I changed this line: If c < "" Then GoTo phred To : If Not c Is Nothing Then GoTo phred And it copied the data from sheet 1 to sheet 2. If you are not using Sheet 3 for anything, you should delete the Dim line and the variable assignment line to conserve memory. "haileybury" wrote: Following is the code I am using to check for a value in sheet1 against sheet 2. If value not found in sheet2 then I want to copy the value from sheet1 and paste it at the bottom of the list of values in sheet2. What is not working is the statement that I am using to copy the row from sheet 1 and pasting it to sheet2 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) Following is the code Private Sub CommandButton2_Click() Dim r As Long 'Last row of data on Sheet 1 (row number) Dim c As Range 'Range/counter Dim Rng As Range 'Row 2 to row 'r' (above) as counter Dim Cel As Range 'Cell value to find from Sheet1 on Sht2 Dim Sht1 As Worksheet 'Sheet1 Dim Sht2 As Worksheet 'Sheet2 Dim Sht3 As Worksheet 'Sheet3 Set Sht1 = Sheets("sheet1") Set Sht2 = Sheets("sheet2") Set Sht3 = Sheets("sheet3") 'Get last row Sheet 1, Column a r = Sht1.Range("a65536").End(xlUp).Row 'Start with Sheet1 Sht1.Activate 'Set 'counter' as first row of data to last row of data (Col a) Set Rng = Range(Cells(2, 1), Cells(r, 1)) 'For cells first to last (counter) For Each Cel In Rng 'find first Sheet1.Cell.Value in sheet 2 With Sht2.Cells Set c = .Find(Cel, LookIn:=xlValues) If c < "" Then GoTo phred r = Sht2.Range("a65536").End(xlUp).Row + 1 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not working... End With phred: MsgBox "went to phred" Next End Sub any tips or recommendations is much appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with syntax for copy and pasting rows across sheets
I tried the statement
"If Not c Is Nothing Then GoTo phred" and the condition is always being evaluated as True and the program skips to the label "phred" even when there is no matching value in sheet2 ( hence why I was originally using If c<"" then goto phred" and it does not even give me the error you mentioned) I am running excel 2002 SP-2. Not sure if this is the issue...? "JLGWhiz" wrote: You should have gotten an error message that the Object Variable was not set if the Find method returned nothing. I changed this line: If c < "" Then GoTo phred To : If Not c Is Nothing Then GoTo phred And it copied the data from sheet 1 to sheet 2. If you are not using Sheet 3 for anything, you should delete the Dim line and the variable assignment line to conserve memory. "haileybury" wrote: Following is the code I am using to check for a value in sheet1 against sheet 2. If value not found in sheet2 then I want to copy the value from sheet1 and paste it at the bottom of the list of values in sheet2. What is not working is the statement that I am using to copy the row from sheet 1 and pasting it to sheet2 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) Following is the code Private Sub CommandButton2_Click() Dim r As Long 'Last row of data on Sheet 1 (row number) Dim c As Range 'Range/counter Dim Rng As Range 'Row 2 to row 'r' (above) as counter Dim Cel As Range 'Cell value to find from Sheet1 on Sht2 Dim Sht1 As Worksheet 'Sheet1 Dim Sht2 As Worksheet 'Sheet2 Dim Sht3 As Worksheet 'Sheet3 Set Sht1 = Sheets("sheet1") Set Sht2 = Sheets("sheet2") Set Sht3 = Sheets("sheet3") 'Get last row Sheet 1, Column a r = Sht1.Range("a65536").End(xlUp).Row 'Start with Sheet1 Sht1.Activate 'Set 'counter' as first row of data to last row of data (Col a) Set Rng = Range(Cells(2, 1), Cells(r, 1)) 'For cells first to last (counter) For Each Cel In Rng 'find first Sheet1.Cell.Value in sheet 2 With Sht2.Cells Set c = .Find(Cel, LookIn:=xlValues) If c < "" Then GoTo phred r = Sht2.Range("a65536").End(xlUp).Row + 1 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not working... End With phred: MsgBox "went to phred" Next End Sub any tips or recommendations is much appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with syntax for copy and pasting rows across sheets
When I used your code on my system, I used two worksheets. On Sheet 1,
Column A, I entered data for several cells. I left sheet 2 blank. Running your code as originally written gave me a runtime error on the line If c < "" Then... for the Object variable not being set. The error occured because c = Nothing and the variable could not be set to nothing. When I changed the statement to evaluate against Nothing, it evaluated to False and executed the copy statement. So that brings us to the question of what do you have on sheet 2 that makes the statement evaluate to true. The way your Find statement is written, you are checking the entire sheet 2. Perhaps you want to narrow the area checked to a specific column or a smaller area of the sheet. The only reason for it to evaluate as true is that it finds a match for the data from sheet 1. Your code is otherwise sound. "haileybury" wrote: I tried the statement "If Not c Is Nothing Then GoTo phred" and the condition is always being evaluated as True and the program skips to the label "phred" even when there is no matching value in sheet2 ( hence why I was originally using If c<"" then goto phred" and it does not even give me the error you mentioned) I am running excel 2002 SP-2. Not sure if this is the issue...? "JLGWhiz" wrote: You should have gotten an error message that the Object Variable was not set if the Find method returned nothing. I changed this line: If c < "" Then GoTo phred To : If Not c Is Nothing Then GoTo phred And it copied the data from sheet 1 to sheet 2. If you are not using Sheet 3 for anything, you should delete the Dim line and the variable assignment line to conserve memory. "haileybury" wrote: Following is the code I am using to check for a value in sheet1 against sheet 2. If value not found in sheet2 then I want to copy the value from sheet1 and paste it at the bottom of the list of values in sheet2. What is not working is the statement that I am using to copy the row from sheet 1 and pasting it to sheet2 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) Following is the code Private Sub CommandButton2_Click() Dim r As Long 'Last row of data on Sheet 1 (row number) Dim c As Range 'Range/counter Dim Rng As Range 'Row 2 to row 'r' (above) as counter Dim Cel As Range 'Cell value to find from Sheet1 on Sht2 Dim Sht1 As Worksheet 'Sheet1 Dim Sht2 As Worksheet 'Sheet2 Dim Sht3 As Worksheet 'Sheet3 Set Sht1 = Sheets("sheet1") Set Sht2 = Sheets("sheet2") Set Sht3 = Sheets("sheet3") 'Get last row Sheet 1, Column a r = Sht1.Range("a65536").End(xlUp).Row 'Start with Sheet1 Sht1.Activate 'Set 'counter' as first row of data to last row of data (Col a) Set Rng = Range(Cells(2, 1), Cells(r, 1)) 'For cells first to last (counter) For Each Cel In Rng 'find first Sheet1.Cell.Value in sheet 2 With Sht2.Cells Set c = .Find(Cel, LookIn:=xlValues) If c < "" Then GoTo phred r = Sht2.Range("a65536").End(xlUp).Row + 1 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not working... End With phred: MsgBox "went to phred" Next End Sub any tips or recommendations is much appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with syntax for copy and pasting rows across sheets
Thanks for your response.
I found the problem, the command button in which I had the macro written was on sheet 2 and in the code I am setting the range to sheet1. Due to this reason was setting the range reference to sheet2 instead of sheet1 i.e it was comparing to itself hence it was failing for the condition "if not c is nothing" I found this when I was very frustrated and decided to swap the sheet names and yahoo it worked fine... Still getting used to the way excel makes references from where the macro is executed. Thanks for your assistance in this matter.... "JLGWhiz" wrote: When I used your code on my system, I used two worksheets. On Sheet 1, Column A, I entered data for several cells. I left sheet 2 blank. Running your code as originally written gave me a runtime error on the line If c < "" Then... for the Object variable not being set. The error occured because c = Nothing and the variable could not be set to nothing. When I changed the statement to evaluate against Nothing, it evaluated to False and executed the copy statement. So that brings us to the question of what do you have on sheet 2 that makes the statement evaluate to true. The way your Find statement is written, you are checking the entire sheet 2. Perhaps you want to narrow the area checked to a specific column or a smaller area of the sheet. The only reason for it to evaluate as true is that it finds a match for the data from sheet 1. Your code is otherwise sound. "haileybury" wrote: I tried the statement "If Not c Is Nothing Then GoTo phred" and the condition is always being evaluated as True and the program skips to the label "phred" even when there is no matching value in sheet2 ( hence why I was originally using If c<"" then goto phred" and it does not even give me the error you mentioned) I am running excel 2002 SP-2. Not sure if this is the issue...? "JLGWhiz" wrote: You should have gotten an error message that the Object Variable was not set if the Find method returned nothing. I changed this line: If c < "" Then GoTo phred To : If Not c Is Nothing Then GoTo phred And it copied the data from sheet 1 to sheet 2. If you are not using Sheet 3 for anything, you should delete the Dim line and the variable assignment line to conserve memory. "haileybury" wrote: Following is the code I am using to check for a value in sheet1 against sheet 2. If value not found in sheet2 then I want to copy the value from sheet1 and paste it at the bottom of the list of values in sheet2. What is not working is the statement that I am using to copy the row from sheet 1 and pasting it to sheet2 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) Following is the code Private Sub CommandButton2_Click() Dim r As Long 'Last row of data on Sheet 1 (row number) Dim c As Range 'Range/counter Dim Rng As Range 'Row 2 to row 'r' (above) as counter Dim Cel As Range 'Cell value to find from Sheet1 on Sht2 Dim Sht1 As Worksheet 'Sheet1 Dim Sht2 As Worksheet 'Sheet2 Dim Sht3 As Worksheet 'Sheet3 Set Sht1 = Sheets("sheet1") Set Sht2 = Sheets("sheet2") Set Sht3 = Sheets("sheet3") 'Get last row Sheet 1, Column a r = Sht1.Range("a65536").End(xlUp).Row 'Start with Sheet1 Sht1.Activate 'Set 'counter' as first row of data to last row of data (Col a) Set Rng = Range(Cells(2, 1), Cells(r, 1)) 'For cells first to last (counter) For Each Cel In Rng 'find first Sheet1.Cell.Value in sheet 2 With Sht2.Cells Set c = .Find(Cel, LookIn:=xlValues) If c < "" Then GoTo phred r = Sht2.Range("a65536").End(xlUp).Row + 1 Cel.EntireRow.Copy destination:=Sht2.Cells(r, 1) ' this is not working... End With phred: MsgBox "went to phred" Next End Sub any tips or recommendations is much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and pasting values of filtered rows | Excel Discussion (Misc queries) | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
Copy and Pasting into new Sheets | Excel Programming | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
Copy and pasting sheets with merged cells | Excel Programming |