VBA Help: Copy data to the next available empty cell in another sheet

[Dclax]

Hello,

I have attempted to create a VBA which takes data from certain cells on a form and copies them into specified cells in another sheet.
The intention is that the code will find the next empty cell of each column.

My problem is that the code appears to just overwrite the cells, it works well the first time and then after that I see issues.

Sub exportdata()'
' exportdata Macro
'
    Range("C4").Select
    Selection.Copy
    Windows("Customer Database.xlsx").Activate
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows("Client Service Form Template.xlsm").Activate
    Range("H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Customer Database.xlsx").Activate
    Range("B1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows("Client Service Form Template.xlsm").Activate
End Sub

Thanks

 

[DanteAmor]

Try this

Note: You must have data in cell C4

Sub Export_Data() '
' exportdata Macro
'
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim u As Double
    
    Set wb1 = Workbooks("Client Service Form Template.xlsm")
    Set wb2 = Workbooks("Customer Database.xlsx")
    
    Set ws1 = wb1.Sheets(1) 'first sheet of template book
    Set ws2 = wb2.Sheets(1) 'first sheet of database book
    
    u2 = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    ws2.Range("A" & u2).Value = ws1.Range("C4").Value
    ws2.Range("B" & u2).Value = ws1.Range("H6").Value
    
End Sub

Validating cell C4

Sub Export_Data()
' exportdata Macro
'
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim u As Double
    
    Set wb1 = Workbooks("Client Service Form Template.xlsm")
    Set wb2 = Workbooks("Customer Database.xlsx")
    
    Set ws1 = wb1.Sheets(1) 'first sheet of template book
    Set ws2 = wb2.Sheets(1) 'first sheet of database book
    
    If ws1.Range("C4").Value = "" Then
        MsgBox "Enter data in C4", vbCritical
        Exit Sub
    End If
    
    u2 = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    ws2.Range("A" & u2).Value = ws1.Range("C4").Value
    ws2.Range("B" & u2).Value = ws1.Range("H6").Value
    
End Sub

 

[Dclax]

Hello,

Thanks, perhaps I should provide more context, because the cell "C4" is actually a checkbox which may sometimes not be filled.
Really what I have created right now is probably a very messy solution for transferring a yes or no value to another sheet depending o if the cell is checked or not.

 

[DanteAmor]

Range("C4").Select
    Selection.Copy

That's what you have in your macro, it means that you're copying cell C4.
Then tell me that the cells you want to copy and where to paste.