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.

 

[My Aswer Is This]

When helping users we always need specific details.
This would require Vba.

You say copy 8 columns of data into Master Column.
You give no sheet name.
But you do not say what 8 columns and what column should be the master column.


And your second question:

My second problem is on another worksheet
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

You do not give the sheet names.


I assume you now see what I mean.

We need specific details like sheet names Table Names and where specifically to copy these to.
If you can provide these details we may be able to help you.

 

[darthbane]

Apologies, thanks for the reply.
for problem 1:

master column is a4:a61 on WorkSsheet Input
all other columns are from b4:i61 on the same WorkSheet

for problem 2:

data that needs to be copied is from a1:O30000
the data doesn't go all the way to row 30,000 depending on the sheet it ranges so i need something that copys the data up until the next blank row then moves on to the other sheet and does the same until all 8 sheets are copied
the sheet names are:
burgundy
charcoal
emerald
magenta
navy
ruby
sapphire
violet

and these need to be copied to worksheet RevRel from a8:O30000

and the 8 sheets data are always changing. so for example charcoal may only have 8 rows of data one day, and the next it will have 15 etc. each week I plan to refresh the data on the master list so that all sheets are compiled together.

I hope that makes sense! and happy new year

 

[My Aswer Is This]

See if this does what you want for problem 1

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To 9
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Lastrow < 4 Then Lastrow = 4
        Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy Cells(Lastrow, 1)
    Next
Application.ScreenUpdating = True
End Sub

 

[darthbane]

See if this does what you want for problem 1

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To 9
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Lastrow < 4 Then Lastrow = 4
        Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy Cells(Lastrow, 1)
    Next
Application.ScreenUpdating = True
End Sub

i tried that but it doesn't seem to output anything...
additional information: the cells that are 'blank' have a formula in them, but it is just outputting a blank.

 

[My Aswer Is This]

The script looks in columns B to I and copies all data found into column "A"

Your original post said:
I have 8 columns of texts (employee names,

You said nothing about formulas.

 

[darthbane]

The script looks in columns B to I and copies all data found into column "A"

Your original post said:
I have 8 columns of texts (employee names,

You said nothing about formulas.

sorry for the confusion.
yes the 8 columns are text (names of staff). however they are being pulled from another workbook. so the formula in each cell is =IF('[workbook1]staffname'!$A3=0,"",'[workbook1]staffname'!$A3) etc

 

[My Aswer Is This]

I believe someone else here at Mr. Excel will need to help you. This is now beyond my knowledgebase.
My script should be copying the formulas. But when copying formulas to other locations can cause problems.
Or do you just want to copy the values and not the formulas? You said "Copy" not copy values only.

If you want to just copy values use this script:

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To 9
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Lastrow < 4 Then Lastrow = 4
        Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy
        Cells(Lastrow, 1).PasteSpecial xlPasteValues
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

sorry for the confusion.
yes the 8 columns are text (names of staff). however they are being pulled from another workbook. so the formula in each cell is =IF('[workbook1]staffname'!$A3=0,"",'[workbook1]staffname'!$A3) etc

 

[darthbane]

I believe someone else here at Mr. Excel will need to help you. This is now beyond my knowledgebase.
My script should be copying the formulas. But when copying formulas to other locations can cause problems.
Or do you just want to copy the values and not the formulas? You said "Copy" not copy values only.

If you want to just copy values use this script:

Sub Copy_Range()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To 9
        Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
        If Lastrow < 4 Then Lastrow = 4
        Range(Cells(4, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Copy
        Cells(Lastrow, 1).PasteSpecial xlPasteValues
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

thanks for the clarification. I found a work around where I set up a macro to copy the values on another sheet


however when utilizing your macro the data does get copied to column A but it does not ignore the blanks instead column b gets copied to row 61 then column c gets copied etc.
and on this sheet the blanks are completely blank (no data,formula , anything)

 

[My Aswer Is This]

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