Excel VBA code to copy spread out data and paste in vertical order

Question

As you can see in the screenshot (I've provided the screenshot link below), I have an excel sheet with lots of numerical data. I have used conditional formatting to highlight some of the data. My end goal is to copy these highlighted data and paste it in vertical order in a new sheet. However, the problem I face is that these data are staggered among many rows and columns. So for instance, there will be a highlighted data in row 120 column BBQ. I want to copy & paste all of these spread out highlighted data in a new sheet in vertical order. I just can't figure out what code to type :(

Any sort of help will be appreciated. Thank You!

"...in a vertical order." - So, would this scan from A1 -> A2 -> A3 ... after Col. A, move to B? Or do you go A1, B1, C1, etc. then down a row? Also, what's the conditional formatting? We could probably take that formula and use it to pull out the data you need. Otherwise, you'll need VBA to pull Highlighted Cells (AFAIK). — BruceWayne, Jul 27 '16 at 20:33
*I just can't figure out what code to type* - what code have you actually tried? You'll get more help if you show your efforts and where it's failing. — Scott Holtzman, Jul 27 '16 at 20:33
@BruceWayne yes it means A1 -> A2 -> A3 and move to B. Not sure what you mean by move to B. Currently the conditional formatting is highlighting any number less than or equal to 50. — A.Munif, Jul 27 '16 at 20:37
@ScottHoltzman I haven't tried any code for copying and pasting the data in the vertical order, because I don't know where to start. I'm still trying to figure out through trial and error and by trying to search something similar in google — A.Munif, Jul 27 '16 at 20:40
@BruceWayne on a second thought I don't think it matters how it scans as long as it can pull out the numbers — A.Munif, Jul 27 '16 at 20:54
But, you do want all your matches in a single column? Or would you be okay with keeping the col. A matches in col. A, B in B, etc? — BruceWayne, Jul 27 '16 at 20:55
Can you add to the end of your post, the Conditional Formatting formula? It makes the VBA part **much** simpler, if we're just looking for values over 50, than a highlight, certain decimal, etc. — BruceWayne, Jul 27 '16 at 21:13
@BurceWayne The values less than or equal to 50 is just an example. In my actual sheet the conditional formatting is linked to Cell D1 where I input 10,20, or 30, etc.. and it will highlight those numbers. I just wanted to figure out how to copy all these spread out numbers and paste in a orderly manner. So to answer your question no it's not just values less than or equal 50 it's less than or equal to values cell D1 — A.Munif, Jul 27 '16 at 21:29
@pnuts yes my entire data set will fit in one column. I only have a little over thousand rows and columns...and the highlighted data (or in this case <= 50) one's will be even lesser — A.Munif, Jul 27 '16 at 22:47

Scott Holtzman · Accepted Answer · 2016-07-27T20:59:07.357

1

Try the below. Make changes to sheet and range names as needed. It loads the data into an array, tests if each point is less than or equal to 50 (moving down the rows, then across the columns ... but you can switch the For statements if you want) and if so, writes to a new sheet.

Sub CopyConditionalData()

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1") ' change as needed
Set ws2 = Worksheets("Sheet2") ' change as needed

Dim rRng As Range
Set rRng = ws1.Range("A1:G100") 'change as needed

Dim aRng As Variant
aRng = rRng

Dim lRows As Long, lCols As Long
For lCols = 1 To rRng.Columns.Count
    For lRows = LBound(aRng) To UBound(aRng)
        If aRng(lRows, lCols) <= 50 Then
            ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1) = aRng(lRows, lCols)
        End If
    Next
Next

End Sub

edited Jul 27 '16 at 20:59

answered Jul 27 '16 at 20:52

Scott Holtzman

27,099
5
37
72

1

Since he just needs those numbers greater than 50, could we use a formula? I'm thinking sort of, but to put all matches in one column would be an unwieldy array formula, if possible at all, yeah? Just trying to work on my formula skills, so am curious – BruceWayne Jul 27 '16 at 20:56
1

good luck with that @BruceWayne - that'd be a real brain-teaser for you :) I can't think of anything I am willing to explore right now (at 5pm on Wednesday!) – Scott Holtzman Jul 27 '16 at 21:01
1

@pnuts - comment from OP *(Currently the conditional formatting is highlighting any number less than or equal to 50)* – Scott Holtzman Jul 27 '16 at 21:11
@pnuts - no problem, I am curious to see a `PivotTable` solution, if that is what you meant by `"PT"` – Scott Holtzman Jul 27 '16 at 21:13
1

@ScottHoltzman thanks a lot! but when I run your code it gives me an object required error. I'll try to fix it but as you mentioned it's 5pm on Wednesday I'll try to figure this out tomorrow morning. – A.Munif Jul 27 '16 at 21:20
@A.Munif - make sure you properly changed the worksheet names and ranges to fit your specific worksheet. If that doesn't fix it, please tell me what line the code errors on? I tested this against a data set just like yours and it worked fully. – Scott Holtzman Jul 27 '16 at 21:31
1

@pnuts - Ah, the old unpivot trick :) Nice suggestion, even if a bit manual. – Scott Holtzman Jul 28 '16 at 13:30
1

@ScottHoltzman Sir, your code works like a charm! I'll probably downsize my data-set because when I tell it to go all the way up to column XFD my PC freezes up haha. – A.Munif Jul 28 '16 at 16:14
@A.Munif - glad you got an answer :) Please mark one as such so those that come after know what worked for you. – Scott Holtzman Jul 28 '16 at 16:19
1

@ScottHoltzman This is the first time I ever asked a question on stack overflow, I was shocked to see all these people reply instantly! I'll probably have to ask follow up questions in the future because this was just first part of a larger VBA code. Hopefully I can figure the rest out (fingers crossed) – A.Munif Jul 28 '16 at 16:25
@A.Munif - SO is great, great resource with many knowledgeable and helpful people. I rarely go elsewhere for help myself. – Scott Holtzman Jul 28 '16 at 18:21

score 0 · Answer 2 · answered Jul 27 '16 at 20:56

0

Assuming you have 120 rows and 100 columns data in Sheet1 and the highlighted color is yellow and the highlighted data has to be copied to Sheet2, then

Dim temp As Integer
temp = 1

For i = 1 To 120
    For j = 1 To 100
        Worksheets("Sheet1").Activate
        If Cells(i, j).Interior.Color = RGB(255, 255, 0) Then
            x = Cells(i, j).Value
            Worksheets("Sheet2").Activate
            Cells(temp, 1).Value = x
            temp = temp + 1
        End If
    Next j
Next i

answered Jul 27 '16 at 20:56

Arun Thomas

805
1
12
21

*used conditional formatting to highlight some of the data* - unfortunately, for your answer, conditional formatting does not change the underlying `.Interior.Pattern` and thus this will not work. – Scott Holtzman Jul 27 '16 at 20:56
1

@Arun Thomas thanks for your input! But as Scott Holtzman mentioned I don't think .Interior.Pattern will work as I have tried something similar but in the form of .Interior.ColorIndex – A.Munif Jul 27 '16 at 21:31

score 0 · Answer 3 · answered Jul 27 '16 at 21:11

0

so .. just copy the values below 50 :]

set cell2 = Worksheets(2).Range("A1")

For Each cell in Worksheets(1).UsedRange
    If Not IsNumeric(cell.Value) And cell.Value <= 50 Then
        cell2.Value2 = cell.Value2
        Set cell2 = cell2.Offset(1)
    End If
Next

answered Jul 27 '16 at 21:11

Slai

22,144
5
45
53

Thanks a lot for your input! – A.Munif Jul 27 '16 at 22:39

score 0 · Answer 4 · edited May 23 '17 at 11:58

0

I suggest removing the Conditional Formatting, labelling the matrix (rows and columns) then unpivoting (for example as shown here).

Then flagging the rows by relevant decade (10, 20, 30 etc ... as in D1) and pivoting the created Table to suit.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 27 '16 at 22:58

pnuts

58,317
11
87
139

1

thanks for the suggestion. But that's the sort of manual work I'm trying to avoid by writing the VBA code. At the end of the day I just want to assign the macro to a button, click it and let excel do all the work for me – A.Munif Jul 28 '16 at 02:49

Excel VBA code to copy spread out data and paste in vertical order

4 Answers4