Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search then copy and past data
To all,
I have taken this code from the MS VBA Help File. How can I modify it so that instead of changing the pattern of the cell, it instead copys the entire row that the cell is in, and pastes it into another workbook called test1.xls, sheet1, cell A2? Thanks in advance, Joseph Crabtree With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternGray50 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search then copy and past data
try this
Do c.entirerow.copy workbooks("test1.xls").sheets("sheet1").range("A2" ) -- Don Guillett SalesAid Software "joecrabtree" wrote in message s.com... To all, I have taken this code from the MS VBA Help File. How can I modify it so that instead of changing the pattern of the cell, it instead copys the entire row that the cell is in, and pastes it into another workbook called test1.xls, sheet1, cell A2? Thanks in advance, Joseph Crabtree With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternGray50 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search then copy and past data
The code you show is for finding multiple instances. If that is the case,
then I assume you wouldn't want subsequent found instances to be copied over ones already copied With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) i = 0 If Not c Is Nothing Then firstAddress = c.Address Do c.entireRow.copy Worksheets("Test1.xls").Worksheets( _ "Sheet1").Range("A1")(i) i = i + 1 Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With -- Regards, Tom Ogilvy "joecrabtree" wrote in message s.com... To all, I have taken this code from the MS VBA Help File. How can I modify it so that instead of changing the pattern of the cell, it instead copys the entire row that the cell is in, and pastes it into another workbook called test1.xls, sheet1, cell A2? Thanks in advance, Joseph Crabtree With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternGray50 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search then copy and past data
A couple of typos: It should be:
With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) i = 1 If Not c Is Nothing Then firstAddress = c.Address Do c.entireRow.copy Worksheets("Test1.xls").Worksheets( _ "Sheet1").Range("A2")(i) i = i + 1 Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... The code you show is for finding multiple instances. If that is the case, then I assume you wouldn't want subsequent found instances to be copied over ones already copied With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) i = 0 If Not c Is Nothing Then firstAddress = c.Address Do c.entireRow.copy Worksheets("Test1.xls").Worksheets( _ "Sheet1").Range("A1")(i) i = i + 1 Set c = .FindNext(c) Loop While c.Address < firstAddress End If End With -- Regards, Tom Ogilvy "joecrabtree" wrote in message s.com... To all, I have taken this code from the MS VBA Help File. How can I modify it so that instead of changing the pattern of the cell, it instead copys the entire row that the cell is in, and pastes it into another workbook called test1.xls, sheet1, cell A2? Thanks in advance, Joseph Crabtree With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternGray50 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
source data editor in Excel 2007 dont seem to allow copy and past. | Charts and Charting in Excel | |||
Macro to Copy/Paste Data into one wksht, skipp one row before past | Excel Discussion (Misc queries) | |||
How to search my past question ? | Excel Worksheet Functions | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) | |||
How do you copy and past data from one spreadsheet to another? | Excel Discussion (Misc queries) |