none
Info transfer from one worksheet to another RRS feed

  • Question

  • Hello,

    I created an excel with 3 different sheets, the first sheet is a platform to put in information that will transfer to another worksheet, I accomplished the information transfer but it is transferring in only one column instead of column and rows. see code below 

    Private Sub CommandButton1_Click()
    Dim Invoice_No As Integer, Customer As String, Time As Date, Project As String, Quantity As Integer, Description As String, Hours As Integer, RateHr As Integer, Amount As Integer
        Worksheets("Sheet1").Select
        Invoice_No = Range("A2")
        Customer = Range("B2")
        Time = Range("C2")
        Project = Range("D2")
        Quantity = Range("E2")
        Description = Range("F2")
        Hours = Range("G2")
        RateHr = Range("H2")
        Amount = Range("I2")
        Worksheets("Sheet3").Select
        Worksheets("Sheet3").Range("A1").Select
        If Worksheets("Sheet3").Range("A1").Offset(1, 0) <> "" Then
        Worksheets("sheet3").Range("A1").End(xlDown).Select
        End If
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Invoice_No
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Customer
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Time
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Project
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Quantity
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Description
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Hours
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = RateHr
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Amount
        Worksheets("Sheet1").Select
        Worksheets("sheet1").Range("A2:I2").ClearContents

    I really appreciate some feedback as I am new to VBA

    THank You 

    Carolina

    Saturday, March 23, 2019 7:11 PM

All replies

  • You don't need to select the sheet and cells in order to copy the data from one sheet to another.

    Please give this a try...

    Private Sub CommandButton1_Click()
    Dim sws As Worksheet, dws As Worksheet
    Dim lr As Long
    
    Application.ScreenUpdating = False
    Set sws = Worksheets("Sheet1")      'Sheet with data to be copied
    Set dws = Worksheets("Sheet3")      'Destination Sheet
    
    'Finding the next empty row on Sheet3
    lr = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    'Copying and pasting data
    sws.Range("A2:I2").Copy dws.Range("A" & lr)
    sws.Range("A2:I2").ClearContents
    Application.ScreenUpdating = True
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Sunday, March 24, 2019 2:27 PM