Stack multiple columns VBA Code

[rathalex]

Hello, I have come across an issue stacking multiple columns in excel 2007. I have 9 columns that need to be stacked up to last non empty cell. The data changes in every column occasionally, so range of each column might be different. There i do need to include Range.End(xlUp) anyhow.... Ive tried range.resize option,but cant seem to get it still. Only was able to stack 2 columns.Any suggestions? Im still new to VBA,so its probably something very simple.

 

[bperrow]

I would need to know both sheet names.
Like copy columns C and D from sheet named "Alpha" and paste into column A of sheet named "Bravo"

thanks for getting back to me so quickly.

so I am trying to copy all cells with data from sheet ‘Working’ columns E & F and paste them in to column A in sheet ‘merged’.

thank you

 

[My Aswer Is This]

thanks for getting back to me so quickly.

so I am trying to copy all cells with data from sheet ‘Working’ columns E & F and paste them in to column A in sheet ‘merged’.

thank you

Try this: Remember sheet names must be exact.
You said "Working" and "merged"

Sub Copy_Columns()
'Modified 6/19/2020 1:58:23 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Working").Cells(Rows.Count, "E").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets("merged").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Working").Cells(1, "E").Resize(Lastrow).Copy Sheets("merged").Cells(1, 1)
Lastrowa = Sheets("merged").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrow = Sheets("Working").Cells(Rows.Count, "F").End(xlUp).Row
Sheets("Working").Cells(1, "F").Resize(Lastrow).Copy Sheets("merged").Cells(Lastrowa, 1)
Application.ScreenUpdating = True
End Sub

 

[bperrow]

Try this: Remember sheet names must be exact.
You said "Working" and "merged"

Sub Copy_Columns()
'Modified 6/19/2020 1:58:23 PM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Working").Cells(Rows.Count, "E").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets("merged").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Working").Cells(1, "E").Resize(Lastrow).Copy Sheets("merged").Cells(1, 1)
Lastrowa = Sheets("merged").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrow = Sheets("Working").Cells(Rows.Count, "F").End(xlUp).Row
Sheets("Working").Cells(1, "F").Resize(Lastrow).Copy Sheets("merged").Cells(Lastrowa, 1)
Application.ScreenUpdating = True
End Sub

thank you so much - it works a treat

 

[My Aswer Is This]

thank you so much - it works a treat

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[RodrigoAbreu]

Hi,
I need help to develop a similar VBA function as per above but with some peculiarities.
for each "stack up" I need to repeat the data from column A until U which carries noun numeric data and stack up the data starting from column V until AG whch carries numerical data from Jan to Dec (12 columns) Also, I need to do it by carrying the format of the cells, colors....
There is anyone that can help me with this VBI?
Regards

Rodrigo

 

[My Aswer Is This]

Hi,
I need help to develop a similar VBA function as per above but with some peculiarities.
for each "stack up" I need to repeat the data from column A until U which carries noun numeric data and stack up the data starting from column V until AG whch carries numerical data from Jan to Dec (12 columns) Also, I need to do it by carrying the format of the cells, colors....
There is anyone that can help me with this VBI?
Regards

Rodrigo

You should start a new posting. Thanks

 

[Tyjyt]

Hi My Aswer Is This! Thanks for helping with the above thread! I'm doing something similar as well, in terms of stacking specific columns into one! However, I'm stumbled on an issue which I hope you could help me out with.

The data that I'm stacking contains formulas, and it's presented in a % format. Do you know if there is any way to copy, and paste the values (e.g 10%, 14%) into another column while excluding the formulas? I think it might be something along the lines of PasteSpecial Paste:=xlPasteValuesAndNumberFormats, but I cant seem to get the code right

 

[My Aswer Is This]

Hi My Aswer Is This! Thanks for helping with the above thread! I'm doing something similar as well, in terms of stacking specific columns into one! However, I'm stumbled on an issue which I hope you could help me out with.

The data that I'm stacking contains formulas, and it's presented in a % format. Do you know if there is any way to copy, and paste the values (e.g 10%, 14%) into another column while excluding the formulas? I think it might be something along the lines of PasteSpecial Paste:=xlPasteValuesAndNumberFormats, but I cant seem to get the code right

I believe forum rules would say you need to start a new posting so; it would be best to start a new posting with specific details of what you are wanting. I will then be able to help you. Thanks

 

[Tyjyt]

I believe forum rules would say you need to start a new posting so; it would be best to start a new posting with specific details of what you are wanting. I will then be able to help you. ThanksAlrig

I believe forum rules would say you need to start a new posting so; it would be best to start a new posting with specific details of what you are wanting. I will then be able to help you. Thanks

Alright, I will do so!

 

[Tyjyt]

Alright, I will do so

I believe forum rules would say you need to start a new posting so; it would be best to start a new posting with specific details of what you are wanting. I will then be able to help you. Thanks

Hi My Aswer Is This, I've posted a new thread titled as "Multiple (Specific) Columns to be stacked into another Column within the same worksheet (VBA Code)" I'd like to seek your help with this issue... Thank you so much!