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?
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
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
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.
Re: How to move data from one excel spreadsheet to another automatically?
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!