Lookup and copy area

[Foppas]

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

 

[Guest]

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")

 

[Foppas]

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:

 

[Tom Ogilvy]

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


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: