VBA to find more than 1 value, copy and paste to anther sheet

amxtomzo

Active Member
Joined
Nov 7, 2009
Messages
312
Hello and thanks for looking
I am trying to loop through a spreadsheet looking in column 3 for DC01, DC02, DC03...ect
and in between the DCXX, in column B, i am also looking for EDITION,
i need to make a list with the DCXX, followed by each instance of EDiTION and the 8 columns of data
listed in between each instance if DCXX

the DCXX goes from DC01 to DC250

i can only find one or the other
i cant seem to start looking for EDITION when i find DCXX,
then stop looking for EDITION when i find the next DCXX
I can only get all of the DCXX listed, Or all of the EDITIONS listed

Thank you very much

it should look like the following when its done, only a much longer list


ABCDEFGH
2DC01






3
EDITION111211
4
EDITION36254640126612504
5DC02






6
EDITION551255
7
EDITION310110
10
8
EDITION427942855655584
9
EDITION56051625123112101
10DC03






11
EDITION2155191643203119
12
EDITION543784518898748

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 73px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Thats OK we have a couple weeks before this group gets insane

i can copy over all rows with EDITION, and all rows with a DCXX
just cant seem to figure out how to get so it looks like the end results our team needs

i will still be poking through the books and BILL's dvd lessons

might get lucky....fingers crossed
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
This is what I came up with. If you need to keep you original report (source doucument), then copy it to another sheet and use that sheet name in the macro.
Code:
Sub DCEDITION()
Dim sh1 As Worksheet, c As Range, lr As Long, i As Long
Set sh1 = Sheets(1) 'Edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    For i = lr To 1 Step -1
        With sh1
            If .Cells(i, 2) <> "EDITION" And Left(.Cells(i, 3).Value, 2) <> "DC" Then
                Rows(i).Delete
            End If
        End With
    Next
    With sh1
        For Each c In Range("C1", .Cells(Rows.Count, 3).End(xlUp))
            If Left(c.Value, 2) = "DC" Then
                c.Offset(0, -2) = c.Value
                .Range("B" & c.Row).Resize(1, 22).ClearContents
            End If
        Next
    End With
End Sub
What this macro does is eliminate all data except that which you want in your output. It was the easiest way to separate the data, rather than trying to pick it out, copy and paste.
 
Upvote 0
HOLY MACRO BATMAN

i came up with a bunch of this...................
Code:
    If WSD.Cells(i, 2) = "EDITION" Or WSD.Cells(i, 3) = "DC01" _
        Or WSD.Cells(i, 3) = "DC02" Or WSD.Cells(i, 3) = "DC03" _
        Or WSD.Cells(i, 3) = "DC04" Or WSD.Cells(i, 3) = "DC05" _
        Or WSD.Cells(i, 3) = "DC06" Or WSD.Cells(i, 3) = "DC07" _
        Or WSD.Cells(i, 3) = "DC08" Or WSD.Cells(i, 3) = "DC09" _
        Or WSD.Cells(i, 3) = "DC010" Or WSD.Cells(i, 3) = "DC010" Then
        
        WSD.Cells(i, 1).Resize(1, 12).Copy
        
    Worksheets("Listhere").Cells(NEXTROW, 1).PasteSpecial Paste:=xlPasteValues
    NEXTROW = NEXTROW + 1

and then a lot of lean up of unwanted data

BIG Help
BIGGER Thank YOU

Thomas
 
Upvote 0
HOLY MACRO BATMAN

i came up with a bunch of this...................
Code:
    If WSD.Cells(i, 2) = "EDITION" Or WSD.Cells(i, 3) = "DC01" _
        Or WSD.Cells(i, 3) = "DC02" Or WSD.Cells(i, 3) = "DC03" _
        Or WSD.Cells(i, 3) = "DC04" Or WSD.Cells(i, 3) = "DC05" _
        Or WSD.Cells(i, 3) = "DC06" Or WSD.Cells(i, 3) = "DC07" _
        Or WSD.Cells(i, 3) = "DC08" Or WSD.Cells(i, 3) = "DC09" _
        Or WSD.Cells(i, 3) = "DC010" Or WSD.Cells(i, 3) = "DC010" Then
        
        WSD.Cells(i, 1).Resize(1, 12).Copy
        
    Worksheets("Listhere").Cells(NEXTROW, 1).PasteSpecial Paste:=xlPasteValues
    NEXTROW = NEXTROW + 1

and then a lot of lean up of unwanted data

BIG Help
BIGGER Thank YOU

Thomas
Sort of round about, but if it produces what you want, then good.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top