How to use VBA to copy from one column to the next blank column?

[DayTraderKevin]

Hello. I'm pretty new to VBA and would like to copy a range of data from column B, then paste to the next blank column.

In this example, I'd like to copy B2:B4 and paste to C2:C4, then again copy B2:B4 and paste to D2:D4, and so on. There will be existing data in other columns further right. Any advice is appreciated.

	A	B	C	D	E	F
1	Company	Stock Price	9:00	9:30	10:00	Action
2	Apple	$130				Buy
3	Microsoft	$235				Buy
4	Tesla	$125				Sell

 

[DayTraderKevin]

Oh, and the worksheet name is RawData

 

[shinigamilight]

I assumed that there will be further blanks after column F and you want to copy range(B2:B4) there too.

Sub pope()
        Dim k As Integer
        Dim lr, lc As Long
        lc = Range("C1", Range("C1").End(xlToRight)).Columns.Count
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        
        For k = 1 To lc
                If Range("B2").Offset(0, k) = "" Then
                    Range("B2:B" & lr).Copy Range("B2").Offset(0, k)
                        End If
        Next k
End Sub

 

[DayTraderKevin]

I assumed that there will be further blanks after column F and you want to copy range(B2:B4) there too.

Sub pope()
        Dim k As Integer
        Dim lr, lc As Long
        lc = Range("C1", Range("C1").End(xlToRight)).Columns.Count
        lr = Cells(Rows.Count, 1).End(xlUp).Row
       
        For k = 1 To lc
                If Range("B2").Offset(0, k) = "" Then
                    Range("B2:B" & lr).Copy Range("B2").Offset(0, k)
                        End If
        Next k
End Sub

Thanks for helping! This almost works. This macro is pasting my formula in column B repeatedly to all blank columns to the right. I'd like to paste values only, and only to the first available blank column to the right each time I run the macro.

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

Sub Copy_Right()
  Range("B2", Range("B" & Rows.Count).End(xlUp)).Copy
  Range("A2").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub

 

[Alex Blakenburg]

It might also be worth considering using xlPasteValuesAndNumberFormats instead of xlPasteValues.

 

[DayTraderKevin]

Welcome to the MrExcel board!

Does this do what you want?

Sub Copy_Right()
  Range("B2", Range("B" & Rows.Count).End(xlUp)).Copy
  Range("A2").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub

Perfect! Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.