Results 1 to 6 of 6

Thread: How to move data from one excel spreadsheet to another automatically?

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    How to move data from one excel spreadsheet to another automatically?

    Hi,

    I want to know if we can move data from one excel spreadsheet to another is possible or not? Do I need any VB or .net program for this or not?

    Please guide how to do this?

    How to move data from one excel spreadsheet to another automatically?

  2. #2
    Join Date
    May 2008
    Posts
    115

    Re: How to move data from one excel spreadsheet to another automatically?

    Here's the code I would use:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Determine if the Target is in
    'Column 6 and contains data
    If Target.Column = 6 And Target.Cells.Count = 1 Then
    If Target.Value <> "" Then
    'Store Target Address
    MyAddress = Target.Address
    'Find Next Empty Cell In Sheet2 Column A
    NextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
    'Cut and Paste Target Row to Sheet2
    Target.EntireRow.Cut Destination:=Sheets(2).Range("A" & NextRow)
    'Delete Target Row in Sheet1
    Range(MyAddress).EntireRow.Delete
    End If
    End If
    End Sub

  3. #3
    Join Date
    May 2008
    Posts
    2,015

    Re: How to move data from one excel spreadsheet to another automatically?

    Here you need to define is a common path where you are storing the database and all the users should have access to the folder where database is saved...please find enclosed macro,your file which is working and database...

    Code:
     
    Sub Submit_Request()
       Dim objForm As Worksheet, objRequests As Worksheet
       Dim targetrow As Range
       Dim DAOdBase As DAO.Database
       Dim DAORecSet As DAO.Recordset
       Dim xRow As Long, xCol As Integer
       Dim xDelete As String, exrange As Range
       Dim i As Long
       
       
       
       
       
        Set objForm = Sheets("Enter")
        
        If Trim(objForm.Cells(7, "D").Value) = "" Then
            objForm.Cells(7, "D").Select
            MsgBox "Please enter the Card number"
            Exit Sub
        End If
        
        If Trim(objForm.Cells(5, "H").Value) = "" Then
            objForm.Cells(5, "H").Select
            MsgBox "Please enter Client"
            Exit Sub
        End If
        
        If Trim(objForm.Cells(7, "H").Value) = "" Then
            objForm.Cells(7, "H").Select
            MsgBox "Please enter User"
            Exit Sub
        End If
            
        If Trim(objForm.Cells(9, "D").Value) = "" Then
            objForm.Cells(9, "D").Select
            MsgBox "Please enter Option 1"
            Exit Sub
        End If
        
        If Trim(objForm.Cells(11, "D").Value) = "" Then
            objForm.Cells(11, "D").Select
            MsgBox "Please enter Requested by"
            Exit Sub
        End If
        
        If Trim(objForm.Cells(13, "D").Value) = "" Then
            objForm.Cells(13, "D").Select
            MsgBox "Please enter some comments"
            Exit Sub
        End If
        
        If Trim(objForm.Cells(5, "L").Value) = "" Then
            objForm.Cells(5, "L").Select
            MsgBox "Please enter status"
            Exit Sub
          ElseIf UCase(Trim(objForm.Cells(5, "L").Value)) = "RESOLVED" Then
             ' determine target sheet
              Set objRequests = Sheets("Resolved")
          Else
              Set objRequests = Sheets("Pending")
        End If
        On Error Resume Next
        Application.EnableEvents = False
        Set targetrow = objRequests.Cells(objRequests.Cells(6000, "A").End(xlUp).Row + 1, "A")
        
        With targetrow
          .Offset(0, 0).Value = objForm.Cells(22, "D").Value
          .Offset(0, 1).Value = objForm.Cells(5, "D").Value
          .Offset(0, 2).Value = objForm.Cells(7, "D").Value
          .Offset(0, 3).Value = objForm.Cells(5, "H").Value
          .Offset(0, 4).Value = objForm.Cells(7, "H").Value
          .Offset(0, 5).Value = objForm.Cells(9, "D").Value
          .Offset(0, 6).Value = objForm.Cells(9, "H").Value
          .Offset(0, 7).Value = objForm.Cells(11, "D").Value
          .Offset(0, 8).Value = objForm.Cells(11, "H").Value
          .Offset(0, 9).Value = objForm.Cells(13, "D").Value
          .Offset(0, 11).Value = objForm.Cells(5, "L").Value
       End With
     
      i = objRequests.Cells(65536, "a").End(xlUp).Row
       Set k = objRequests.Range("A" & i & ":N" & i)
       ActiveWorkbook.Names.Add Name:="Raw1", RefersTo:=k
      
    Application.EnableEvents = True
    xpath = "Your Path where database is saved\db1.mdb"
     
    Set DAOdBase = DBEngine.OpenDatabase(xpath)
     
    xrange = "Raw1"
    Set exrange = Range(xrange)
     
    xtable = "btable"
     
     
    Set DAORecSet = DAOdBase.OpenRecordset(xtable)
     
    For xRow = 1 To exrange.Rows.Count
        DAORecSet.AddNew
        For xCol = 1 To exrange.Columns.Count
            DAORecSet.Fields(xCol) = exrange.Cells(xRow, xCol).Value
        Next xCol
        DAORecSet.Update
    Next xRow
     
     
     MsgBox "Your Complaint Ref is " & objForm.Cells(22, "D")
        
        ' Now clear the form
        objForm.Cells(7, "D").Value = ""
        objForm.Cells(9, "D").Value = ""
        objForm.Cells(5, "H").Value = ""
        objForm.Cells(7, "H").Value = ""
        objForm.Cells(9, "H").Value = ""
        objForm.Cells(11, "D").Value = ""
        objForm.Cells(19, "E").Value = ""
        objForm.Cells(11, "H").Value = ""
        objForm.Cells(13, "D").Value = ""
        objForm.Cells(5, "L").Value = ""
     
        
    End Sub

  4. #4
    Join Date
    Aug 2008
    Posts
    475

    Re: How to move data from one excel spreadsheet to another automatically?

    i got kinda confused....

    but you should be able to just highlight the range you want moved (B4:G200), right click and choose "Move", and it should prompt you to choose either "new worksheet" or "new workbook"...im not exactly sure what it asks you. either that, or just try copy and paste. oh, and to move only certain rows, highlight the first one you want moved, hold down "ctrl" and highlight the rest of the rows, then do the first steps i told.

  5. #5
    Join Date
    Jun 2008
    Posts
    144

    Re: How to move data from one excel spreadsheet to another automatically?

    Please have a look at this
    http://www.office-addins.com/-excel-...assistant.html
    I hope this helps you!

  6. #6
    Join Date
    Jun 2008
    Posts
    97

    Re: How to move data from one excel spreadsheet to another automatically?

    Make a copy of the worksheet (select the sheet, right click on the sheets tab, select the "Move or Copy" option, and check the "Create a copy" option. On the new created sheet select all cells (it should have all the data on) select all the cells (click on the square on the left of the column A, and above the 1st Row heading. Right click \Copy, right click paste special and select value or values and number format!

Similar Threads

  1. What is the formula to copy or move a spreadsheet
    By vALaNCiA in forum MS Office Support
    Replies: 1
    Last Post: 02-02-2012, 03:53 PM
  2. cannot copy data from one spreadsheet to another
    By fANIBHUSAN in forum Windows Software
    Replies: 4
    Last Post: 31-03-2011, 08:15 PM
  3. Excel 2010 spreadsheet automatically create and rename
    By cHYNA in forum Windows Software
    Replies: 4
    Last Post: 15-01-2011, 02:12 AM
  4. How to Import all text data in Excel spreadsheet
    By racer in forum Tips & Tweaks
    Replies: 1
    Last Post: 06-01-2010, 09:38 PM
  5. Export data from excel spreadsheet to an Oracle Table
    By Kurtz in forum Windows Software
    Replies: 2
    Last Post: 13-05-2009, 01:11 PM

Posting Permissions

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