Copying data from one tab to another using criteria and alternate rows 2

[s0crates9]

Hi. I have a pretty difficult question, at least to me. I am used to Excel and using Formulas, but when it comes to VBA, I am a noob.

Here is what I have been trying to accomplish to no avail.

I have an Excel workbook with two sheets. Once is called "Source", the other is called "Display". The "Source" tab has a bunch of manual entries - in particular column A has numberical data (1-100), column B has website URL's (

http://www.testsite.com),

and column C has a dropdown value of either "yes", "no" or a blank.

What I want to do is create an array if you will of all rows that have "Yes" in Column C. The tricky part is that I want them copied over to the "Display" tab one by one (in order of selection) but on evey other row. Here is an example of the "Source" tab:

Column A        Column B                    Column C
1               [URL unfurl="true"]http://www.test1.com[/URL]        Yes
2               [URL unfurl="true"]http://www.test2.com[/URL]
3               [URL unfurl="true"]http://www.test3.com[/URL]        Yes
4               [URL unfurl="true"]http://www.test4.com[/URL]        Yes
5               [URL unfurl="true"]http://www.test5.com[/URL]        Yes
6               [URL unfurl="true"]http://www.test6.com[/URL]        Yes
7               [URL unfurl="true"]http://www.test7.com[/URL]
8               [URL unfurl="true"]http://www.test8.com[/URL]
9               [URL unfurl="true"]http://www.test9.com[/URL]
10              [URL unfurl="true"]http://www.test10.com[/URL]       Yes

So the jist here is that row B1,B3,B4,B5,B6 and B10 are all qualified to be copied over to the "Display" tab. Now here is how I want the data to be placed:

Column A        Column B
1               [URL unfurl="true"]http://www.test1.com[/URL]
2               SKIPPED ROW
3               [URL unfurl="true"]http://www.test3.com[/URL]
4               SKIPPED ROW
5               [URL unfurl="true"]http://www.test4.com[/URL]
6               SKIPPED ROW
7               [URL unfurl="true"]http://www.test5.com[/URL]
8               SKIPPED ROW
9               [URL unfurl="true"]http://www.test6.com[/URL]
10               SKIPPED ROW
11              [URL unfurl="true"]http://www.test10.com[/URL]
12               SKIPPED ROW

Every other row should have the data sequentially from the "Source" tab. I tried to do this in formulas and using conditional formatting, but they didn't work, and according to what I tried, the only way to do this is using VBA.

I appreciate help with this, an example would be ideal as I have wasted so much time and effort trying out different methods and I need to get this to work - I am dealing with tons on data and this would cut some hours by automating it.

Thanks!

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/

 

[AnotherHiggins]

First: Use the Macro Recorder (Tools > Macro > Record New Macro) to get code for using AutoFilter to select only the 'YES' rows.

Once you have only the rows you want displayed (with the macro recorder still on), copy all cells to the Display tab.

Now all that's left is to place an empty row between each existing row.

Here's an example of how to do that:

For i = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row To 2 Step -1
    Rows(i).Insert
Next i

Post back with the resulting code if you need any help cleaning it up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Question: What numbers do you need in column A of the Display sheet? Do you actually want 1-12 in order (based on your example) or do you need 1, blank, 3, blank, 4, blank, 5, blank, 6, blank, 10?

If you only need to do this once the quick way is to:
1) filter the data on the source sheet, filtering for a "Yes" in column C.
2) copy the data to Display sheet and select the last row (control+down will get you there quick)
3) Run the following macro:

Sub insertrows()
For x = ActiveCell.Row To 2 Step -1
    Cells(x, 1).EntireRow.Insert
Next x
End Sub

Depending on the column A requirement you may need to do some renumbering.




Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[DirkStruan]

John - You beat me to the punch!

D

 

[s0crates9]

Thanks for the quick reply John.

I actually want to keep the data in order without using autofilter, as the data is organized by section and autofilter would jumble that data.

I was hoping to have these elements all in one function or routine. It's a combination of selecting only rows that include "Yes" in column C and then placing them in order on the "Display" tab, alternating rows so that data appears on row 1 first, then row 3, then row 5 and so on. The data in the "Source" tab should be left as is with no shuffling or filtering.

Thanks for your help.

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/

 

[AnotherHiggins]

Woo Hoo! Good thing I ate my wheaties this morning.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Ok select a cell in the source data and run the following:

Sub transportdata()
r = ActiveCell.CurrentRegion.Rows.Count
z = 1
For x = 1 To r
    If Cells(r, 3) = "Yes" Then
        Sheets("Display").Cells(z, 2).Value = Cells(x, 2).Value
        z = z + 2
    End If
Next x
End Sub

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[AnotherHiggins]

The autofilter will not shuffle the order of the entries.

Go through the steps I have outlined. It will do exactly what you want and will run much faster than looping through each row on the Source sheet.

You can simply turn off the autofilter on the Source sheet and it will appear exactly as it did before you started. All of this can be done with the macro.

Post back what you got out of the Macro Recorder[attn]*[/attn] and we'll help you clean it up.

[attn]*[/attn] To get to the VBEditor, press [Alt]+[F11]. If you can't find the code, post back.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[s0crates9]

Dirk,

Great suggestion! It works well there is only one thing.
The loop for "Yes" pulls nothing and when I get rid of the quotation marks, it pulls every result. Do you know if there is something missing? It is a literal "Yes" in the colum, no quotations or any other marks.

Thanks as well John for your input - very valuable. I just have a preference to use this method though.

Thanks!

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/

 

[DirkStruan]

Parameter error!

Should have been x not r in the following:

If Cells(x, 3) = "Yes" Then

Sorry!

D

 

[s0crates9]

Thanks Dirk!

That worked like a charm. Thanks so much.

Cordially - Phil

Web site design, internet marketing, SEO and business solutions company.

http://www.startegy.net

http://www.dominus-web.com

http://websitedesigncompany.blogspot.com/