CODEWITHSUNDEEP
vbaexcel
Mohsin Khan
Mohsin Khan

Reputation: 31

How to program command button to copy a range of data to another workbook

I wonder if someone can help me please. I am a new VBA user and made a timesheet using guidelines on this website.

Currently, when the command button 2 is clicked, the data captured on the timesheet is transferred to sheet called 'Data' within the same worksheet. What I want is to transfer it to another workbook's sheet saved in another folder. Full path of sheet is ‪C:\Users\mohskhan\Desktop\masterts.xlsm. Please can someone help.

Current coding is as follows:

Private Sub CommandButton2_Click()

ComboBox1.Enabled = True

Dim ssheet As Worksheet

Set ssheet = ThisWorkbook.Sheets("Data")

nr = ssheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

ssheet.Cells(nr, 1) = CDate(Me.TextBox1)
ssheet.Cells(nr, 2) = (Me.TextBox2)
ssheet.Cells(nr, 3) = (Me.ComboBox1)
ssheet.Cells(nr, 4) = (Me.ComboBox2)
ssheet.Cells(nr, 5) = (Me.TextBox3)
ssheet.Cells(nr, 6) = (Me.TextBox4)
ssheet.Cells(nr, 7) = (Me.TextBox5)
ssheet.Cells(nr, 8) = (Me.TextBox12)
ssheet.Cells(nr, 9) = (Me.ComboBox3)
ssheet.Cells(nr, 11) = Evaluate("=NOW()-TODAY()")
ssheet.Cells(nr, 14) = (Me.TextBox35)
ssheet.Cells(nr, 21) = (Me.TextBox6)
ssheet.Cells(nr, 22) = (Me.ComboBox4)
ssheet.Cells(nr, 23) = (Me.TextBox7)
ssheet.Cells(nr, 24) = (Me.TextBox23)

ssheet.Cells(nr, 25) = (Me.TextBox8)
ssheet.Cells(nr, 26) = (Me.ComboBox5)
ssheet.Cells(nr, 27) = (Me.TextBox9)
ssheet.Cells(nr, 28) = (Me.TextBox24)

ssheet.Cells(nr, 29) = (Me.TextBox10)
ssheet.Cells(nr, 30) = (Me.ComboBox6)
ssheet.Cells(nr, 31) = (Me.TextBox11)
ssheet.Cells(nr, 32) = (Me.TextBox25)
ssheet.Cells(nr, 34) = (Me.TextBox36)
ssheet.Cells(nr, 35) = (Me.TextBox37)



ComboBox1 = ""
ComboBox2 = ""
ComboBox3 = ""
TextBox3 = ""
TextBox4 = ""
TextBox12 = ""
TextBox5 = ""
ComboBox4 = ""
ComboBox5 = ""
ComboBox6 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox23 = ""
TextBox24 = ""
TextBox25 = ""
TextBox35 = ""
TextBox36 = ""
TextBox37 = ""
CommandButton1.Enabled = False
CommandButton2.Enabled = False


End Sub

Upvotes: 1

Views: 126

Answers (2)

user6432984
user6432984

Reputation:

Updated:

Private Sub CommandButton2_Click()
    Const FullName = "C:\Users\Owner\Downloads\masterts.xlsm"
    Dim CloseWorkbook As Boolean
    Dim WB As Workbook

    ComboBox1.Enabled = True

    On Error Resume Next
    Set WB = Workbooks("masterts.xlsm")
    CloseWorkbook = Err.Number = 0
    On Error GoTo 0

    If WB Is Nothing Then Set WB = Workbooks.Open(FullName)

    With WB.Worksheets("Data")
        With .Range("A" & .Rows.Count).End(xlUp).Offset(1)
            .Resize(1, 13).Value = Array(CDbl(CDate(Me.TextBox1)), Me.TextBox2, Me.ComboBox1.Value, Me.ComboBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, _
                                         Me.TextBox5.Value, Me.TextBox12.Value, Me.ComboBox3.Value, CDbl(TimeValue(Now)), Me.TextBox35.Value, Me.TextBox6.Value, Me.ComboBox4.Value)
        End With
        With .Range("U" & .Rows.Count).End(xlUp).Offset(1)
            .Resize(1, 12).Value = Array(Me.TextBox7.Value, Me.TextBox23.Value, Me.TextBox8.Value, Me.ComboBox5.Value, Me.TextBox9.Value, Me.TextBox24.Value, Me.TextBox10.Value, Me.ComboBox6.Value, Me.TextBox11.Value, Me.TextBox25.Value, Me.TextBox36.Value, Me.TextBox37.Value)
        End With
        .Save
    End With

    If CloseWorkbook Then WB.Close SaveChanges:=False
    CommandButton1.Enabled = False
    CommandButton2.Enabled = False

End Sub

Upvotes: 1

MJV
MJV

Reputation: 350

Should do the trick

Workbooks.Open("C:\Users\mohskhan\Desktop\masterts.xlsm")

Set ssheet = Workbooks("masterts.xlsm").Worksheets("Data")

'Run code

EDIT: I suggest turning off screen updating while you execute the data transfer

Application.ScreenUpdating = False 

'Code to execute

Application.ScreenUpdating = True

Upvotes: 1

Related Questions