merge columns with text into one master range

[darthbane]

Hi guys, I have a problem finding a solution to an excel tracker I am creating for work.

I have 8 columns of texts (employee names, that can all vary in column length) I need to copy them all into a master column.

for instance

Gary	Lisa	Emily	Rob
Kim	Sophia	Andy	Ryan
Dennis	Paul	Rudd	Kate
	Nish	Alex	brad
	Priya	Joyce	greg
		tony

<tbody>
</tbody>

I need these to go into one column (without any blanks). On my company computer we use Excel 2010 and cannot download any add-ons including power query.
I would like a non-vba solution for this as this workbook will be used by others without any vba knowledge but if it cannot be avoided a VBA solution is helpful.

My second problem is on another worksheet I need to do something similar

I will be pulling data from 8 tables all with the same headings from diffrent sheets and want to copy them all into one master table
I believe VBA is the only solution for this and I am ok with that. I have figured the VBA code will need to be select A8 (on the master sheet) and copy table range from worksheet 1, select next blank row and copy table from worksheet 2, etc.

your help will be most appreciated!

again for the second option power query is a no-go.

 

[darthbane]

Try this:

Sub Copy_Range()
'Modified 1-2-2018 12:22 AM EST
Application.ScreenUpdating = False
Dim c As Range
Dim ss As Long
ss = 4
    For i = 2 To 9
        For Each c In Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i))
            If c.Value <> "" Then
                c.Copy
                Cells(ss, 1).PasteSpecial xlPasteValues: ss = ss + 1
            End If
        Next
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

works perfectly! thanks you are a rockstar!!

now for my second problem is there a similar solution that can be applied? where just the values are copied from the 8 worksheets up until the last blank (" ") row at which point it moves onto the next worksheet ? in each worksheet the data is from A:O:

 

[My Aswer Is This]

Now to your second question.

Is the name of the sheet where you want all this data copied to: Named "RevRel"

And you want to start pasting in Range ("A8")

And Can I just assume were copying from all the sheets in the workbook from Sheet(2) to the last sheet.
And sheet(RevRel) is sheet (1)

Meaning this sheet is in the far left position on your tab bar.

This way I do not have to indicate sheets by their names

And are we copying all ranges from these other sheets or just ranges with values. Not copying blank cells.

So we are copying Range("A1:O" & Lastrow)

Lastrow being lastrow in column "A"

Or do you not want to copy row (1) in each sheet?

 

[darthbane]

Now to your second question.

Is the name of the sheet where you want all this data copied to: Named "RevRel"

And you want to start pasting in Range ("A8")

And Can I just assume were copying from all the sheets in the workbook from Sheet(2) to the last sheet.
And sheet(RevRel) is sheet (1)

Meaning this sheet is in the far left position on your tab bar.

This way I do not have to indicate sheets by their names

And are we copying all ranges from these other sheets or just ranges with values. Not copying blank cells.

So we are copying Range("A1:O" & Lastrow)

Lastrow being lastrow in column "A"

Or do you not want to copy row (1) in each sheet?

yes, the master sheet is named 'RevRel' and would like to starting pasting in range A8


no - RevRel is the 3rd sheet in the WB, with the other sheets going from 7-14 (however sheets 7-14 will be hidden)
the names of those sheets are:
burgundy
charcoal
emerald
magenta
navy
ruby
sapphire
violet
(in that order)


if it helps I can move 'RevRel' sheet to sheet 6 if that makes it easier.


we are just copying ranges with values (however there may be blank cells i will give an example below)


im not sure what you meant by the last part but I believe I will want to copy row (1) in each sheet.



a1

open

Dec 1 2017

andy

Not cheked

yes

no

no

yes

no

no

yes

Closed

Dec 3

Dwight

checked

No

Yes

Yes

no

Yes

Yes

no

Open

Dec 5

Micheal

Checked

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

closed

Dec 10

jim

Not checked

No

Yes

No

No

No

No

<tbody>
</tbody>

as you can see there may be blank cells within the rows but id like the data to stop copying at the last blank row (where column A is blank)
in this example there are 4 rows to copy before it stops and moves to the next sheet, where the other may have 5 rows to copy, and the sheet after may have 0 rows to copy etc.
if there is data in the row- Column A cell will always have a value
hope that makes sense.

 

[My Aswer Is This]

You can try this:

Your wanting to copy data from sheets: (7 to 14)
Into sheet named "RevRel"

And your saying sheets 7 to 14 are hidden.
I'm not use to dealing with hidden sheets so try this and see what happens.
The sheets being hidden do not change their numbers.

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("RevRel").Activate
    For i = 7 To 14
        Lastrow = Sheets("RevRel").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        Sheets(i).Range("A1:O" & Lastrowa).Copy Sheets("RevRel").Range("A" & Lastrow)
    Next
Application.ScreenUpdating = True
End Sub

 

[darthbane]

You can try this:

Your wanting to copy data from sheets: (7 to 14)
Into sheet named "RevRel"

And your saying sheets 7 to 14 are hidden.
I'm not use to dealing with hidden sheets so try this and see what happens.
The sheets being hidden do not change their numbers.

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets("RevRel").Activate
    For i = 7 To 14
        Lastrow = Sheets("RevRel").Cells(Rows.Count, "A").End(xlUp).Row + 1
        Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
        Sheets(i).Range("A1:O" & Lastrowa).Copy Sheets("RevRel").Range("A" & Lastrow)
    Next
Application.ScreenUpdating = True
End Sub

no.. it seems to skip to the last sheet and it copies all the data including the blank rows until row 48000
again with this range the data is pulled from another workbook so there are formulas that output a text. i just need the value copied over, in this instance it is copying the entire formula..

 

[darthbane]

actually when testing on another copy.

all 8 sheets seem to copy but
sheet 1 starts off at a16 instead of a1 and copys all the blank rows until the end of the range (row 6000)
than sheet 2 starts copying from row6000 onwards etc.

 

[My Aswer Is This]

I suggest you post a new question to the forum and explain in detail what your wanting.

I helped on the first part but do not believe I can help any more on the second part.
Sorry I was not a able to help.

 

[darthbane]

I suggest you post a new question to the forum and explain in detail what your wanting.

I helped on the first part but do not believe I can help any more on the second part.
Sorry I was not a able to help.

will do. and no you have done more than enough! I am really grateful for all the time you put in and appreciate how quick you were to come back with solutions.
have a wonderful new year bud.

 

[Mike Szczesny]

Here is a crazy complicated formula. However, it is robust. Depending on how you use the =int, =mod, =row, and =column parts you can vary how you want to results to be. Use Cntr+Shift+Enter. Here is the formula.

=INDEX($A$1:$D$6,INT(SMALL(IF($A$1:$D$6<>"",(ROW($A$1:$D$6)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$6)-COLUMN($A$1)+1),ROWS($F$1:F1))/10^9),MOD(SMALL(IF($A$1:$D$6<>"",(ROW($A$1:$D$6)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$6)-COLUMN($A$1)+1),ROWS($F$1:F1)),10^9)) Gary Lisa Emily Rob Gary Kim Sophia Andy Ryan Lisa Dennis Paul Rudd Kate Emily Nish Alex Brad Rob Priya Joyce Greg Kim Tony Sophia Andy Ryan Dennis Paul Rudd Kate Nish Alex Brad Priya Joyce Greg Tony <colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>