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

[mfh1287]

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]

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]

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

Worked perfectly!

 

[kevin9999]

Worked perfectly!

Good to hear, and thanks for the feedback

 

[KieranO]

Worked perfectly!

Good to hear, and thanks for the feedback

Hi,

Ive come across your post which is exactly what i have been looking for. i edited it slightly to fit my needs however i get an error that a object or with variable is undefined with the LCol line under the Dim, could you offer any help?

Sub CopyToDashboard()
  
    Dim wsData As Worksheet
        Set wsData = Worksheets("Format sheet")
    Dim wsDashboard As Worksheet
        Set wsDashboard = Worksheets("Quarterly Report")
    Dim r As Range
        Set r = wsDashboard.Range("C3:BB74")
    Dim LCol As Long
        LCol = r.Cells.Find("*", , xlFormulas, , 2, 2).Column + 1
  
    If LCol < 53 Then
        wsData.Range("F11:F82", wsData.Cells(Rows.Count, "F").End(xlUp)).Copy
        wsDashboard.Cells(3, LCol).PasteSpecial (xlPasteValues)
        Application.CutCopyMode = False
    Else
        MsgBox "The Dashboard range is already full"
        Exit Sub
    End If
End Sub