VBA Help: Code to transfer data to another sheet into the next available, empty row

Dclax

New Member
Joined
Feb 21, 2019
Messages
4
Hello all,

I am a newbie with VBA and new to this forum, so please excuse any poor terminology.
I have created a form that I fill in when working with customers and I have created a VBA which copies the filled in data to another sheet.

The problem I am facing is that my code for pasting into the next empty row / cell of that column doesn't consistently work. It continues to overwrite what was previously copied and pasted.

Here is what I have so far:

Code:
Sub CheckBox()
    If (Range("B15").Value = True) Then
        Windows("Customer Database.xlsx").Activate
        Range("F1").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "Yes"
        Windows("Client Service Form Template.xlsm").Activate
    Else
        Windows("Customer Database.xlsx").Activate
        Range("F1").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "No"
        Windows("Client Service Form Template.xlsm").Activate
  
    End If


    If (Range("D15").Value = True) Then
        Windows("Customer Database.xlsx").Activate
        Range("G1").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "Yes"
        Windows("Client Service Form Template.xlsm").Activate
    Else
        Windows("Customer Database.xlsx").Activate
        Range("G1").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "No"
        Windows("Client Service Form Template.xlsm").Activate
  
    End If
End Sub

Thanks!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Try replacing this :
Code:
Range("F1").End(xlDown).Offset(1, 0).Select
With this :
Code:
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Select
And similarly for column G.

If this does what you want, your macro can probably be reduced to :
Code:
Sub CheckBox()
With Workbooks("Customer Database.xlsx")
    .Cells(Rows.Count, "F").End(xlUp)(2) = IIf([B15], "Yes", "No")
    .Cells(Rows.Count, "G").End(xlUp)(2) = IIf([D15], "Yes", "No")
End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,407
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top