Copy from one workbook to another

K

Karen53

Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub
 
D

Dave Peterson

Maybe specifying the complete path to the file that should be opened would help:

Set wbkCopyFrom = Workbooks.Open("test.xls")

would look more like:

Set wbkCopyFrom _
= Workbooks.Open("C:\Documents and Settings\David Peterson\Desktop\test.xls")

for me.

If test.xls is already open, does your code work ok? If no, what line causes
the error?
Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub
Click to expand...
 
K

Karen53

Hi Dave,

It doesn't whether the file is open or not. I'll try it with the complete
path.

By the way, does all of the code for a worksheet calculate procedure have to
reside in the worksheet module or can it call out to a general module?
--
Thanks for your help.
Karen53


Dave Peterson said:
Maybe specifying the complete path to the file that should be opened would help:

Set wbkCopyFrom = Workbooks.Open("test.xls")

would look more like:

Set wbkCopyFrom _
= Workbooks.Open("C:\Documents and Settings\David Peterson\Desktop\test.xls")

for me.

If test.xls is already open, does your code work ok? If no, what line causes
the error?
Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
 
D

Dave Peterson

You can call a procedure in a different module in the worksheet_Calculate event.
Hi Dave,

It doesn't whether the file is open or not. I'll try it with the complete
path.

By the way, does all of the code for a worksheet calculate procedure have to
reside in the worksheet module or can it call out to a general module?
--
Thanks for your help.
Karen53

Dave Peterson said:
Maybe specifying the complete path to the file that should be opened would help:

Set wbkCopyFrom = Workbooks.Open("test.xls")

would look more like:

Set wbkCopyFrom _
= Workbooks.Open("C:\Documents and Settings\David Peterson\Desktop\test.xls")

for me.

If test.xls is already open, does your code work ok? If no, what line causes
the error?
Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

If the file is open, then what line causes that error message?
Hi Dave,

It doesn't whether the file is open or not. I'll try it with the complete
path.

By the way, does all of the code for a worksheet calculate procedure have to
reside in the worksheet module or can it call out to a general module?
--
Thanks for your help.
Karen53

Dave Peterson said:
Maybe specifying the complete path to the file that should be opened would help:

Set wbkCopyFrom = Workbooks.Open("test.xls")

would look more like:

Set wbkCopyFrom _
= Workbooks.Open("C:\Documents and Settings\David Peterson\Desktop\test.xls")

for me.

If test.xls is already open, does your code work ok? If no, what line causes
the error?
Hi,

I have two workbooks in a folder on my desktop. I want to copy from one
workbook to the other workbook. It tells me it cannot locate the file. It
did run once but I have not been able to get it to run again. I have tried
the path various ways with no success. Have I missed something?

Here is my code:

Sub wkbookCreate()

Dim wbkCopyFrom As Workbook
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim wbkName As String

On Error Resume Next

Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else
Application.ScreenUpdating = False

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])

'Pool lists
'CAM
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(Tablespg.Name, _
"'",
"''"))).Range("J4:J21")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(Tablespg.Name, _

"'", "''"))).Range("J4:J21")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(Tablespg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])

ThisWorkbook.Sheets((Replace(GrossUpspg.Name, "'", "''"))). _
Unprotect
Password:=([MyPassword])
'Cam Pools list 2
Set rngCopyFrom = wbkCopyFrom.Sheets((Replace(GrossUppg.Name, _
"'",
"''"))).Range("B15:J16")
Set rngCopyTo = ThisWorkbook.Sheets((Replace(GrossUppg.Name, +
"'",
"''"))).Range("B15:J16")
rngCopyTo.Value = rngCopyFrom.Value

ThisWorkbook.Sheets((Replace(GrossUppg.Name, "'", "''"))). _
Protect
Password:=([MyPassword])
End If
End If
Done:
Application.ScreenUpdating = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Range of Object Worksheet failed 2
Chk sheet exits, copy all to another book. 7
Global Variables and Constants 17
Two Open Workbooks 6
Error in Macro 4
VBA to copy from one workbook into another 0
Copy Multiple Areas from One Workbook to Another 4
If product already exists continued 2

Top