Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Copy from one workbook to another
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 333
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to copy an image (or picture) from one workbook to a new sheetin another workbook Ruchir Excel Worksheet Functions 1 July 25th 08 07:29 AM
Copy data from Workbook Alpha & reorganize it in Workbook Bravo u473 Excel Programming 1 October 31st 07 02:37 AM
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook burl_rfc Excel Programming 1 April 1st 06 08:48 PM
Copy a range of cells in an unopened workbook and paste it to the current workbook topstar Excel Programming 3 June 24th 04 12:50 PM


All times are GMT +1. The time now is 10:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement