PDA

View Full Version : Search for empty column / paste values only?



pingwin77
03-26-2009, 08:07 AM
I have the following workbook and need a macro for it.

1. copy all the data from sheet 2, column B
2. Search for the next open Column on sheet 3 by looking across row 2
3. Paste special / Values only in the open column starting on row 2. I need to keep the dta in row 1 to label my information.

I know how to do the first part or even record the macro to do the copy and paste special / values. I do not know hoe to search across a row for the next open column. Can someone explain this or just write a quick macro that I can attach to a button?

Oh. I will have the button on sheet 3 so I can execute the macro from there.

Thanks a ton!

Simon Lloyd
03-26-2009, 10:49 AM
I haven't looked at your worksheet but this should do what you need!
Dim rng As Range, Newrng As Range
Set rng = Sheets("Sheet2").Range("B2:B" & Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row)
Set Newrng = Sheets("Sheet3").Cells(2, Sheets("Sheet3").Range("IV2").End(xlToLeft).Column).Offset(0, 1)
rng.Copy
With Newrng
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
With Sheets("Sheet1")
Application.CutCopyMode = False
End With

mdmackillop
03-26-2009, 02:36 PM
A little simplification

Set Newrng = Sheets("Sheet3").Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1)

Simon Lloyd
03-26-2009, 02:41 PM
Good catch Malcolm, that makes it more xl2007 compliant!