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!
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!
VBA Code:
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
Last edited by a moderator: