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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top