ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy from one workbook to another (https://www.excelbanter.com/excel-programming/407615-copy-one-workbook-another.html)

Karen53
Copy from one workbook to another
 
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

--
Thanks for your help.
Karen53

Dave Peterson
Copy from one workbook to another
 
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?

Karen53 wrote:

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

--
Thanks for your help.
Karen53

--

Dave Peterson

Karen53
Copy from one workbook to another
 
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" wrote:

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?

Karen53 wrote:

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

--
Thanks for your help.
Karen53

--

Dave Peterson

Dave Peterson
Copy from one workbook to another
 
You can call a procedure in a different module in the worksheet_Calculate event.

Karen53 wrote:

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" wrote:

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?

Karen53 wrote:

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

--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Copy from one workbook to another
 
If the file is open, then what line causes that error message?

Karen53 wrote:

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" wrote:

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?

Karen53 wrote:

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

--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com