Copying data from one spreadsheet to another using Macro and ActiveControl Button

Question

I'm trying to write a macro which will copy data from one spreadsheet to another spreadsheet within the same workbook using Marco assigned to a active control button. Also, I would the button to clear the data from fields after copying to the other sheet. However, I'm struggling to Debug this code. Can anyone offer assistance?

Private Sub CommandButton1_Click()
    Dim OrderDate As String, Job As String, AccountManager As String
    Dim Site As String, DueDate As String, BudgetedHours As String
    `enter code here`Supervisor As String
    Dim TotalPieces As String, Billedhours As String, UnitsCompleted As String
    Dim EmployeeName As String, Task As String
    Dim StartTime As String, FinishTime As String, TotalTime As String
    Dim Notes As String

    Worksheets("Assembly Work Form").Select
    Date = Range("B3")
    Job = Range("B4")
    CustomerName = Range("B5")
    AccountManager = Range("B7")
    Supervisor = Range("B8")
    Site = Range("B9")
    DueDate = Range("B10")
    BudgetedHours = Range("B11")
    TotalPieces = Range("F5")
    Billedhours = Range("F3")
    UnitsCompleted = Range("F6")
    EmployeeName = Range("B15")
    Task = Range("B15")
    StartTime = ("E17")
    FinishTime = ("G17")
    TotalTime = ("I17")
    Notes = Range("K17")

    Worksheets("AssemblyTotals").Select
    Worksheets("AssemblyTotals").Range("A2").Select

    If Worksheets("AssemblyTotals").Range("A3").Offset("1,0") <> "" Then
        Worksheets("AssemblyTotals").Range("A2").End(xlDown).Select
    End If

    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = Date
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Job
    ActiveCell.Offset(-1, 0).Select`enter code here`
    ActiveCell.Value = CustomerName
    Worksheets("Assembly Work Form").Select
    Worksheets("Assembly Work Form").Range("DataFields").ClearContents
End Sub

Can you clarify why you're using `Range("B15")` twice? Also, do you only want to "copy" the `Date`, `Job` and `CustomerName`? In short, some extra context would be welcome. Last but not least, avoid [select and activeCell etc.](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Rik Sportel, Oct 30 '17 at 09:17
@RikSportel Yes, I would like to copy the date, job, customerName, and etc...to another spreadsheet every time a user clicks the active control button. This separate spreadsheet will log and store all information. — Lance Herrod, Oct 31 '17 at 20:00

score 0 · Answer 1 · answered Oct 30 '17 at 11:26

Basically, what you want to do is the following:

Private Sub CommandButton1_Click()
    Worksheets("AssemblyTotals").Range("SomeTargetAddress").Value = Worksheets("Assembly Work Form").Range("SomeSourceAddress").Value 'Copy a value
    Worksheets("Assembly Work Form").Range("SomeSourceAddress").ClearContents 'Clear the original value
End Sub

Of course change the "SomeTargetAddress" and "SomeSourceAddress" to valid cell references. Also keep in mind you can do multiple cells at once:

Private Sub CommandButton1_Click()
    Worksheets("AssemblyTotals").Range("A2:A100").Value = Worksheets("Assembly Work Form").Range("A2:A100").Value 'Do 99 cells at once
End Sub

And if you want to start on the first empty row:

Private Sub CommandButton1_Click()
    Dim wr as Long 'Variable to store the row
    wr = Worksheets("AssemblyTotals").Range("A2").End(xlDown).Row + 1
    Worksheets("AssemblyTotals").Range("A" & wr).Value = Worksheets("Assembly Work Form").Range("A2:A100").Value 'Do 99 cells at once starting at the first empty row in Column A.
End Sub

No need to store everything in variables first, unless you intend to use those variables in more places later on. Also, note that you shouldn't use ActiveCell and Select, etc. as mentioned in my comment.

Based on the above, all you have to do now is just map the cells correctly and that's it.

Copying data from one spreadsheet to another using Macro and ActiveControl Button

1 Answers1