VBA stacking multiple columns and pasting with paste special

[RobFL]

Hi,

I have 5 columns I need to stack in one column on a separate sheet (columns AD, AE, AF, AG and AH). Using the forum I was able to find code to copy two columns from the "CQ All Facts Report" sheet and stack it to column A on the "MemberText" sheet. I was just going to follow the same logic for the other three columns. The columns are not going to be the same length each time the sheet is updated with new data. The problem I am encountering is the columns on the CQ All Facts Report are formulas. I have been trying to add paste special to it so it only pastes the values but I cannot seem to figure out where to add that without getting an error. Here is the code that copies over everything including the formulas. I was trying to add pastespecial xlpastevalues after the Cells references but it is not working. Is there a way to modify this to have it paste values rather than the formulas? Thanks in advance!

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("CQ All Facts Report").Cells(Rows.Count, "AD").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets("MemberText").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("CQ All Facts Report").Cells(2, "AD").Resize(Lastrow).Copy Sheets("MemberText").Cells(1, 1)
Lastrowa = Sheets("MemberText").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrow = Sheets("CQ All Facts Report").Cells(Rows.Count, "AE").End(xlUp).Row
Sheets("CQ All Facts Report").Cells(2, "AE").Resize(Lastrow).Copy Sheets("MemberText").Cells(Lastrowa, 1)
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Copy_Columns()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("CQ All Facts Report").Cells(Rows.Count, "AD").End(xlUp).Row
Dim Lastrowa As Long
With Sheets("MemberText")
   Lastrowa = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   .Cells(Lastrowa, 1).Resize(Lastrow).Value = Sheets("CQ All Facts Report").Cells(2, "AD").Resize(Lastrow).Value
   Lastrowa = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   Lastrow = Sheets("CQ All Facts Report").Cells(Rows.Count, "AE").End(xlUp).Row
   .Cells(Lastrowa, 1).Resize(Lastrow).Value = Sheets("CQ All Facts Report").Cells(2, "AE").Resize(Lastrow).Value
End With
Application.ScreenUpdating = True
End Sub

 

[RobFL]

Hi & welcome to MrExcel.
How about

Sub Copy_Columns()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("CQ All Facts Report").Cells(Rows.Count, "AD").End(xlUp).Row
Dim Lastrowa As Long
With Sheets("MemberText")
   Lastrowa = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   .Cells(Lastrowa, 1).Resize(Lastrow).Value = Sheets("CQ All Facts Report").Cells(2, "AD").Resize(Lastrow).Value
   Lastrowa = .Cells(Rows.Count, "A").End(xlUp).Row + 1
   Lastrow = Sheets("CQ All Facts Report").Cells(Rows.Count, "AE").End(xlUp).Row
   .Cells(Lastrowa, 1).Resize(Lastrow).Value = Sheets("CQ All Facts Report").Cells(2, "AE").Resize(Lastrow).Value
End With
Application.ScreenUpdating = True
End Sub

Thanks for your help and quick response! That worked and I really appreciate it. Thanks for teaching me the new set up.

 

[Fluff]

You're welcome & thanks for the feedback.