Copy column if row 1 has x and paste to next empty cell on sheet 2

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:21 PM GMT

Question

I need to figure out how to write the code to do the following in excel. On sheet 1 I need to copy the column if it has an x in row 1. There is nothing else in any cells i row 1. Then I need to paste the column into the next empty column on sheet 2. On sheet 2 row 1 will always have data in it if there is anything anywhere in the column. I found alot of places online where people copy the rows to the next empty row but can't seem to find where anyone is copying the column to the next empty column. Can you help? Please let me know if you need more information.

Answer

angele,

this worked for me. You showed your sheet names as having a space in them, so that is the way I wrote the code:

Sub copycolumns() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range Set sh1 = Worksheets("Sheet 1") Set sh2 = Worksheets("Sheet 2") Set r2 = sh2.Cells(1, Columns.Count).End(xlToLeft) Set r2 = r2.Offset(0, 1) Set r1 = sh1.Rows(1).SpecialCells(xlConstants, xlTextValues) If Not r1 Is Nothing Then

 Set r1 = Intersect(sh1.Rows, r1.EntireColumn)
 r1.Copy r2

End If

End Sub

Advertisement

©2022 eLuminary LLC. All rights reserved.