|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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! |
![]() |
|
Tags: data, dot net, excel, spreadsheet |
Thread Tools | Search this Thread |
|
![]() | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What is the formula to copy or move a spreadsheet | vALaNCiA | MS Office Support | 1 | 02-02-2012 04:53 PM |
cannot copy data from one spreadsheet to another | fANIBHUSAN | Windows Software | 4 | 31-03-2011 08:15 PM |
Excel 2010 spreadsheet automatically create and rename | cHYNA | Windows Software | 4 | 15-01-2011 03:12 AM |
How to Import all text data in Excel spreadsheet | racer | Tips & Tweaks | 1 | 06-01-2010 10:38 PM |
Export data from excel spreadsheet to an Oracle Table | Kurtz | Windows Software | 2 | 13-05-2009 01:11 PM |