+ Reply to Thread
Results 1 to 2 of 2

Save Data code not saving to the next line, keeps overwriting to same line.

Hybrid View

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Afghanistan
    MS-Off Ver
    Excel 2010
    Posts
    13

    Question Save Data code not saving to the next line, keeps overwriting to same line.

    I am trying to make this code auto save data to the next row but it wont work any help appreciated.

    Option Explicit
    Dim wsData     As Worksheet
    Dim wsForm     As Worksheet
    Dim rNextCl    As Range
    Sub Save_Data1()
    
        'Set variables
        Set wsData = Sheet5
        Set wsForm = Sheet1
        'find the next empty row for data input
        Set rNextCl = wsData.Cells(65536, 1).End(xlUp).Offset(1, 0)
    
        'enter the data using Offset to find next Column
        'note use With....End With to let Excel that the following lines are working with rNextCl
        'also reduces typing!!
    
        With rNextCl
            .Value = wsForm.Cells(2, 2).Value
            .Offset(0, 2).Value = wsForm.Cells(9, 4).Value
            .Offset(0, 3).Value = wsForm.Cells(78, 4).Value
            .Offset(0, 4).Value = wsForm.Cells(78, 6).Value
            .Offset(0, 5).Value = wsForm.Cells(78, 8).Value
            .Offset(0, 6).Value = wsForm.Cells(78, 10).Value
            .Offset(0, 7).Value = wsForm.Cells(78, 12).Value
            .Offset(0, 8).Value = wsForm.Cells(78, 14).Value
            
             
            End With
    
        'confirm data transferred
        MsgBox "Data transferred successfully" & vbCrLf & "", vbInformation, "Data transfer"
    
    
    End Sub
    Last edited by Justin25150; 05-31-2012 at 11:10 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Save Data code not saving to the next line, keeps overwriting to same line.

    Does your data extend to more than 65536 rows? If so, its better to use ......Cells(Rows.Count,1).End(xlup) .....

    Is column A empty for the last otherwise filled row on your sheet? If so, you will get overwriting as described.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1