# VBA to paste data in next empty column starting in a specific cell?



## mfh1287 (Dec 22, 2022)

Hi, I'm trying to paste data into another sheet in the first available column, starting in cell C4. If C4 is not empty, then I would want the code to paste the data in D4, etc. Here's what I have but it's not working as intended. I have attached an image of where I'd like the data to be inserted (highlighted in yellow)






Sub CopyAndClear()

Dim wsData As Worksheet Dim wsDashboard As Worksheet

Set wsData = ThisWorkbook.Sheets("Data") Set wsDashboard = ThisWorkbook.Sheets("Dashboard")

' Copy values from "Data" sheet Column O starting in row 3 wsData.Range("O3:O" & wsData.Cells(wsData.Rows.Count, "O").End(xlUp).Row).Copy

' Find first available column in "Dashboard" sheet starting in cell C4 Dim firstColumn As Long firstColumn = wsDashboard.Cells(4, wsDashboard.Columns.Count).End(xlToLeft).Column + 1

' Paste values in "Dashboard" sheet starting in first available column wsDashboard.Cells(4, firstColumn).PasteSpecial xlPasteValues

End Sub


----------



## kevin9999 (Dec 23, 2022)

Try this on a *copy *of your data.  It assumes your Dashboard range is fixed.

```
Option Explicit
Sub CopyToDashboard()
    Dim wsDashboard As Worksheet, wsData As Worksheet
    Set wsDashboard = Worksheets("Dashboard")
    Set wsData = Worksheets("Data")
    
    Dim r As Range
    Set r = wsDashboard.Range("B4:F9")
    Dim LCol As Long
    LCol = r.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
    
    If LCol < 7 Then
        wsData.Range("O3", wsData.Cells(Rows.Count, "O").End(xlUp)).Copy
        wsDashboard.Cells(4, LCol).PasteSpecial (xlPasteValues)
        Application.CutCopyMode = False
    Else
        MsgBox "The Dashboard range is already full"
        Exit Sub
    End If
End Sub
```


----------



## mfh1287 (Dec 23, 2022)

kevin9999 said:


> Try this on a *copy *of your data.  It assumes your Dashboard range is fixed.
> 
> ```
> Option Explicit
> ...


Worked perfectly!


----------



## kevin9999 (Dec 23, 2022)

mfh1287 said:


> Worked perfectly!


Good to hear, and thanks for the feedback 🙂


----------

