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



## DayTraderKevin (Dec 23, 2022)

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*CompanyStock Price9:009:3010:00Action*2*Apple$130Buy*3*Microsoft$235Buy*4*Tesla$125Sell


----------



## DayTraderKevin (Dec 23, 2022)

Oh, and the worksheet name is RawData


----------



## shinigamilight (Dec 24, 2022)

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 (Dec 24, 2022)

shinigamilight said:


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


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 (Dec 25, 2022)

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 (Dec 25, 2022)

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


----------



## DayTraderKevin (Dec 25, 2022)

Peter_SSs said:


> Welcome to the MrExcel board!
> 
> Does this do what you want?
> 
> ...


Perfect! Thank you!


----------



## Peter_SSs (Dec 25, 2022)

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


----------

