Hello all and thank in advance for your patience!!!

Please bare with me whilst I try and explain what I am trying to do.

I am trying to write some code that will add data from a worksheet to
two different workbooks.

The 'data entry' workbook will have the following fields:
Date Ref no Issued to Issued by Checked by

If possible I would like the data entered under each of these fields
to:

1. a workbook called 'aerial survey data'. For this I would like the
data to be added and stored to the next blank row.

2. a workbook called 'report data'. This workbook will be linked to a
Word document and the data will be used to automatically enter report
details in the Word document. Each time the code is run in Excel I
would like the data in this workbook to be overwritten.

I plan to run the code by assigning the macro a shortcut key.

Once the data has been entered (i.e. the shortcuy key pressed and the
code run) it would be good if the data was cleared from the data entry
workbook.

I have tried to create my own code using various websites (for example
http://www.meadinkent.co.uk/xl-update-list.htm) but cannot make this
work. Is there some kind soul out there who can help me with or write
the code for me.

Many, many thanks

Paul

Well, I won't bare with you (it's too darn cold), but one way:

Public Sub ProcessData()
Const sPATH As String = "<your path"
Const sFILE1 As String = "aerial survey data.xls"
Const sFILE2 As String = "report data.xls"
Const sERR As String = "Error opening file" & vbNewLine & _
vbNewLine & "$$" & vbNewLine & vbNewLine & "No data copied"
Dim wbOne As Workbook
Dim wbTwo As Workbook
Dim rCopy As Range
Set rCopy = ThisWorkbook.Sheets(2).Range("A2:E2")
On Error Resume Next
OpenFIle sFILE1, sPATH
Set wbOne = Workbooks(sFILE1)
OpenFIle sFILE2, sPATH
Set wbTwo = Workbooks(sFILE2)
On Error GoTo 0
If Not wbOne Is Nothing Then
If Not wbTwo Is Nothing Then
With wbOne.Sheets(1)
.Cells(.Rows.Count, 1).End(xlUp).Offset( _
1, 0).Resize(1, rCopy.Columns.Count).Value = _
rCopy.Value
End With
wbTwo.Sheets(1).Cells(1, 1).Resize( _
1, rCopy.Columns.Count).Value = rCopy.Value
rCopy.ClearContents
wbTwo.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
wbOne.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
End Sub

Private Sub OpenFIle(sFileName As String, _
Optional sFilePath As String)
Dim wbTemp As Workbook
On Error Resume Next
Set wbTemp = Workbooks(sFileName)
If wbTemp Is Nothing Then _
Workbooks.Open Filename:=sFilePath & sFileName
On Error GoTo 0
End Sub

Adjust your sheet and range references to suit.


In article ,
(Paul Marshall) wrote:

Hello all and thank in advance for your patience!!!

Please bare with me whilst I try and explain what I am trying to do.

I am trying to write some code that will add data from a worksheet to
two different workbooks.

The 'data entry' workbook will have the following fields:
Date Ref no Issued to Issued by Checked by

If possible I would like the data entered under each of these fields
to:

1. a workbook called 'aerial survey data'. For this I would like the
data to be added and stored to the next blank row.

2. a workbook called 'report data'. This workbook will be linked to a
Word document and the data will be used to automatically enter report
details in the Word document. Each time the code is run in Excel I
would like the data in this workbook to be overwritten.

I plan to run the code by assigning the macro a shortcut key.

Once the data has been entered (i.e. the shortcuy key pressed and the
code run) it would be good if the data was cleared from the data entry
workbook.

I have tried to create my own code using various websites (for example
http://www.meadinkent.co.uk/xl-update-list.htm) but cannot make this
work. Is there some kind soul out there who can help me with or write
the code for me.

Many, many thanks

Paul

Correction: The last "sFILE2" should be "sFILE1"

In article ,
JE McGimpsey wrote:

Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
End Sub

JE McGimpsey wrote in message ...
Correction: The last "sFILE2" should be "sFILE1"

In article ,
JE McGimpsey wrote:

Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
End Sub

Many, many thanks for this - I will give the coding a go tonight and
let you know how it goes tomorrow. Will no doubt have a new set of
questions!

All the best

Paul

Fallen at the first hurdle!

Tried to run the code but couldn't get it past the first few lines. I
think something to do with what I entered as 'my path' as I get an
error message saying error opening file. Any hints on what I should be
entering here?

I would like to take this up one level (oh know I hear you cry!) and
use multiple sheets on the data entry file to update to mulitple
sheets on the aerial survey file. How would the code change for this
purpose.

Would it be simpler for me to email the workbooks for you (somebody)
to look at?

I really apprecaite any help with this - it has been driving me mad
for over a week!

Best wishes

Paul

<your path should be the path to your file...

For instance

Const sPATH As String = "Z:\test\"

In article ,
(Paul Marshall) wrote:

Tried to run the code but couldn't get it past the first few lines. I
think something to do with what I entered as 'my path' as I get an
error message saying error opening file. Any hints on what I should be
entering here?

Something like (not validated):

Public Sub ProcessData()
Const sPATH As String = "<your path"
Const sFILE1 As String = "aerial survey data.xls"
Const sFILE2 As String = "report data.xls"
Const sERR As String = "Error opening file" & vbNewLine & _
vbNewLine & "$$" & vbNewLine & vbNewLine & "No data copied"
Dim wbOne As Workbook
Dim wbTwo As Workbook
Dim rCopy As Range
Dim i As Long
On Error Resume Next
OpenFIle sFILE1, sPATH
Set wbOne = Workbooks(sFILE1)
OpenFIle sFILE2, sPATH
Set wbTwo = Workbooks(sFILE2)
On Error GoTo 0
If Not wbOne Is Nothing Then
If Not wbTwo Is Nothing Then
For i = 1 to ThisWorkbook.Worksheets.Count
Set rCopy = ThisWorkbook.Sheets(i).Range("A2:E2")
With wbOne.Sheets(1)
.Cells(.Rows.Count, 1).End(xlUp).Offset( _
1, 0).Resize(1, rCopy.Columns.Count).Value = _
rCopy.Value
End With
wbTwo.Sheets(1).Cells(1, 1).Resize( _
1, rCopy.Columns.Count).Value = rCopy.Value
rCopy.ClearContents
Next i
wbTwo.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
wbOne.Close SaveChanges:=True
Else
MsgBox Application.Substitute(sERR, "$$", sPATH & sFILE2)
End If
End Sub

Private Sub OpenFIle(sFileName As String, _
Optional sFilePath As String)
Dim wbTemp As Workbook
On Error Resume Next
Set wbTemp = Workbooks(sFileName)
If wbTemp Is Nothing Then _
Workbooks.Open Filename:=sFilePath & sFileName
On Error GoTo 0
End Sub


In article ,
(Paul Marshall) wrote:

Fallen at the first hurdle!

Tried to run the code but couldn't get it past the first few lines. I
think something to do with what I entered as 'my path' as I get an
error message saying error opening file. Any hints on what I should be
entering here?

I would like to take this up one level (oh know I hear you cry!) and
use multiple sheets on the data entry file to update to mulitple
sheets on the aerial survey file. How would the code change for this
purpose.

Would it be simpler for me to email the workbooks for you (somebody)
to look at?

I really apprecaite any help with this - it has been driving me mad
for over a week!

Best wishes

Paul

In article ,
(Paul Marshall) wrote:

Would it be simpler for me to email the workbooks for you (somebody)
to look at?

It probably would. I can send you a consultancy agreement if you like.
My standard rate is US$50/half hour, but I give substantial volume
discounts...<g