Paste range to first column, then next blank column

[horizonflame]

Hi All,

I have found my formula for pasting a copied range to the next blank column, but need some help if the next blank column is the first column in the sheet. With my current formula it pastes to column B not A.

Sheets("DataRaw").Select
Application.Goto Reference:="R2C1:R7000C2,R2C7:R7000C7"
Selection.Copy

Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, 1).PasteSpecial xlValues
Application.CutCopyMode = False

Many thanks

 

[Mentor82]

Hi,
How about this?

DIM LastCol&
Dim OffsetNo&
Sheets("DataRaw").Select
Application.Goto Reference:="R2C1:R7000C2,R2C7:R7000C7"
Selection.Copy

LastCol=Sheets("DatesRaw").Range("A3").End(xlToLeft).Column
If LastCol = 1 and Sheets("DatesRaw").Range("A1")="" then
    OffsetNo=0
Else
    OffsetNo=1
End if
Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, OffsetNo).PasteSpecial xlValues
Application.CutCopyMode = False

 

[Fluff]

This line

Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, 1).PasteSpecial xlValues

will paste into B3 regardless of how many columns have data.
Will row 3 always have data?

 

[horizonflame]

Will row 3 always have data?

Hi Fluff,

Row 1 and 2 are headers. Row 3 starts empty.

Thanks

 

[Fluff]

In that case try

With Sheets("DatesRaw")
   If .Cells(3, 1) = "" Then
      .Cells(3, 1).PasteSpecial xlPasteValues
   Else
      .Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
   End If
End With

 

[horizonflame]

Thanks Fluff, that works great!

Thank you Mentor82 for your solution

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mentor82]

My pleasure. I'm glad we could help you. Thansk for you feedback