Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and copy area
Hi, can someone please help me with a function or a macro that can look
for a value "V65-1" and copy everything until another value "V65-2"-1 row from sheet1 to sheet2 Hope that you smart people understand my question. Thanks/ Oscar Tried to attach my excel-sheet but it seems like I can't, so I hope this will help! V65-1 1 LA CHAAYA'S FANNY s6 2 SÄVAFLICKAN s9 3 SPOON HORNLINE s5 4 POLS VALBORG s7 5 ARCTIC ICE* (DE) s7 6 KICKAN SILVIO s8 7 REBECKA s7 8 ELI SEA s8 V65-2 1 OLIVER HACKSTABLE v5 2 ORSO h4 3 KAIN ZET v4 4 BUMBLEBEE MEJL s6 5 REGISSEUR (NO) h4 6 ELECTRIC RUNNER h4 7 LUCKY BLEAU v6 8 ELI ENTRPRISE s4 9 HUGG LLOYD v6 10 MADERRIFIC (US) s4 11 KASINO (DK) h4 12 PURPLE SEA (DE) h8 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and copy area
Dim rng as Range, rng1 as Range set rng = Columns(1).specialCells(xlconstants) set rng1 = rng.Areas(1).End(xldown)(0) Range(rng.Areas(1),rng1).Resize(,2).copy Worksheets("Sheet2").Range("A1") -- Regards, Tom Ogilvy "Foppas" wrote: Hi, can someone please help me with a function or a macro that can look for a value "V65-1" and copy everything until another value "V65-2"-1 row from sheet1 to sheet2 Hope that you smart people understand my question. Thanks/ Oscar Tried to attach my excel-sheet but it seems like I can't, so I hope this will help! V65-1 1 LA CHAAYA'S FANNY s6 2 SÄVAFLICKAN s9 3 SPOON HORNLINE s5 4 POLS VALBORG s7 5 ARCTIC ICE* (DE) s7 6 KICKAN SILVIO s8 7 REBECKA s7 8 ELI SEA s8 V65-2 1 OLIVER HACKSTABLE v5 2 ORSO h4 3 KAIN ZET v4 4 BUMBLEBEE MEJL s6 5 REGISSEUR (NO) h4 6 ELECTRIC RUNNER h4 7 LUCKY BLEAU v6 8 ELI ENTRPRISE s4 9 HUGG LLOYD v6 10 MADERRIFIC (US) s4 11 KASINO (DK) h4 12 PURPLE SEA (DE) h8 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and copy area
Hi Tom,
Thank you for your answer but I don't get it to work, I guess that I have to change rng and rng1 to the values that I'm looking for, or? It just copies the cell"A1" from the first sheet to the next. If you think of anything I would appreciate it. Best regards/ Oscar See changed script: Dim V651 As Range, V652 As Range Set V651 = Columns(1).SpecialCells(xlConstants) Set V652 = V651.Areas(1).End(xlDown)(0) Range(V651.Areas(1), V652).Resize(, 2).Copy Worksheets("Sheet7").Range("A1") Tom Ogilvy skrev: Dim rng as Range, rng1 as Range set rng = Columns(1).specialCells(xlconstants) set rng1 = rng.Areas(1).End(xldown)(0) Range(rng.Areas(1),rng1).Resize(,2).copy Worksheets("Sheet2").Range("A1") -- Regards, Tom Ogilvy "Foppas" wrote: Hi, can someone please help me with a function or a macro that can look for a value "V65-1" and copy everything until another value "V65-2"-1 row from sheet1 to sheet2 Hope that you smart people understand my question. Thanks/ Oscar Tried to attach my excel-sheet but it seems like I can't, so I hope this will help! V65-1 1 LA CHAAYA'S FANNY s6 2 SÄVAFLICKAN s9 3 SPOON HORNLINE s5 4 POLS VALBORG s7 5 ARCTIC ICE* (DE) s7 6 KICKAN SILVIO s8 7 REBECKA s7 8 ELI SEA s8 V65-2 1 OLIVER HACKSTABLE v5 2 ORSO h4 3 KAIN ZET v4 4 BUMBLEBEE MEJL s6 5 REGISSEUR (NO) h4 6 ELECTRIC RUNNER h4 7 LUCKY BLEAU v6 8 ELI ENTRPRISE s4 9 HUGG LLOYD v6 10 MADERRIFIC (US) s4 11 KASINO (DK) h4 12 PURPLE SEA (DE) h8 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and copy area
with the data laid out like (which is how I saw it in your original email)
Column A Column B AAAA A1 A2 A3 A4 A5 A6 A7 BBBB B1 B2 B3 B4 B5 B6 B7 CCCC Original code worked fine for me. In your code Range(V651.Areas(1), V652).Resize(, 2).Copy Worksheets("Sheet7").Range("A1") should be one line or Range(V651.Areas(1), V652).Resize(, 2).Copy _ Worksheets("Sheet7").Range("A1") with a continuation character - but that may just be wordwrap in your email - hard to say. -- Regards, Tom Ogilvy "Foppas" wrote in message oups.com... Hi Tom, Thank you for your answer but I don't get it to work, I guess that I have to change rng and rng1 to the values that I'm looking for, or? It just copies the cell"A1" from the first sheet to the next. If you think of anything I would appreciate it. Best regards/ Oscar See changed script: Dim V651 As Range, V652 As Range Set V651 = Columns(1).SpecialCells(xlConstants) Set V652 = V651.Areas(1).End(xlDown)(0) Range(V651.Areas(1), V652).Resize(, 2).Copy Worksheets("Sheet7").Range("A1") Tom Ogilvy skrev: Dim rng as Range, rng1 as Range set rng = Columns(1).specialCells(xlconstants) set rng1 = rng.Areas(1).End(xldown)(0) Range(rng.Areas(1),rng1).Resize(,2).copy Worksheets("Sheet2").Range("A1") -- Regards, Tom Ogilvy "Foppas" wrote: Hi, can someone please help me with a function or a macro that can look for a value "V65-1" and copy everything until another value "V65-2"-1 row from sheet1 to sheet2 Hope that you smart people understand my question. Thanks/ Oscar Tried to attach my excel-sheet but it seems like I can't, so I hope this will help! V65-1 1 LA CHAAYA'S FANNY s6 2 SÄVAFLICKAN s9 3 SPOON HORNLINE s5 4 POLS VALBORG s7 5 ARCTIC ICE* (DE) s7 6 KICKAN SILVIO s8 7 REBECKA s7 8 ELI SEA s8 V65-2 1 OLIVER HACKSTABLE v5 2 ORSO h4 3 KAIN ZET v4 4 BUMBLEBEE MEJL s6 5 REGISSEUR (NO) h4 6 ELECTRIC RUNNER h4 7 LUCKY BLEAU v6 8 ELI ENTRPRISE s4 9 HUGG LLOYD v6 10 MADERRIFIC (US) s4 11 KASINO (DK) h4 12 PURPLE SEA (DE) h8 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error: "the copy area & the paste area are not the same size & sh | Excel Discussion (Misc queries) | |||
Area Lookup from Radial button | Excel Discussion (Misc queries) | |||
copy just the plot area? | Charts and Charting in Excel | |||
copy chart area | Excel Programming | |||
Allow for COPY/PASTE in the "Comment" area | Excel Worksheet Functions |