Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Specifying Cells To Copy Across Workbooks Not Working!
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
dim dim is offline
external usenet poster
  
Posts: 123
Default Specifying Cells To Copy Across Workbooks Not Working!
Hi all,

I have two workbooks, Book1 and 2, called 73.xls and 3.xls. Both are opened
before the code below.

73.xls is active when the code reaches this point, and as shown below, the
3rd code line shown is where it switches to 3.xls.

But when it gets to line 9 -- Rows(Cells(n, n).Value).Copy -- it crashes.
I've tried changing the code a few ways but cant get it working. Does anyone
have any ideas?

Sheets("Sheet1").Select

n = Range("A1").Value

Windows("3.xls").Activate

With ThisWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ThisWorkbook.Sheets("Sheet1")
.Protect "1234"
End With

Windows("73.xls").Activate

With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End With
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Specifying Cells To Copy Across Workbooks Not Working!
Change Windows to Workbooks. Change ThisWorkbook
to ActiveWorkbook. ThisWorkbook refers to the workbook
contains the code. Since you are using the Activate
method/style of code then ActiveWorkbook is the safe
way to go.

If it still crashes, tell us what the message says.

Sheets("Sheet1").Select

n = Range("A1").Value

Workbooks("3.xls").Activate

With ActiveWorkbook.Sheets("Sheet1")
..Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ActiveWorkbook.Sheets("Sheet1")
..Protect "1234"
End With

Windows("73.xls").Activate

With ActiveWorkbook.Sheets("DataIn").Rows("3:3")
..PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End With

"dim" wrote:

Hi all,

I have two workbooks, Book1 and 2, called 73.xls and 3.xls. Both are opened
before the code below.

73.xls is active when the code reaches this point, and as shown below, the
3rd code line shown is where it switches to 3.xls.

But when it gets to line 9 -- Rows(Cells(n, n).Value).Copy -- it crashes.
I've tried changing the code a few ways but cant get it working. Does anyone
have any ideas?

Sheets("Sheet1").Select

n = Range("A1").Value

Windows("3.xls").Activate

With ThisWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ThisWorkbook.Sheets("Sheet1")
.Protect "1234"
End With

Windows("73.xls").Activate

With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End With
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default Specifying Cells To Copy Across Workbooks Not Working!

Excel may be crashing because Rows(Cells(n, n).Value) does not exist?
In addition, I see that several of the range callouts have no
workbook qualifier and some ranges have no sheet qualifier.
It is good practice to always specify both unless only one sheet is
in use in the project.
You also don't say what version of XL you are using and where the
code is located. The version in this case probably doesn't make
any difference, but XL2007 does seem to cause a lot of problems.
Note that "ThisWorkbook" is independent of the active window.

I would clean it up and see if the problem goes away, if not post again.
Also, do you have a real name yet? <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"dim"
wrote in message
Hi all,
I have two workbooks, Book1 and 2, called 73.xls and 3.xls. Both are opened
before the code below.

73.xls is active when the code reaches this point, and as shown below, the
3rd code line shown is where it switches to 3.xls.

But when it gets to line 9 -- Rows(Cells(n, n).Value).Copy -- it crashes.
I've tried changing the code a few ways but cant get it working. Does anyone
have any ideas?

Sheets("Sheet1").Select

n = Range("A1").Value

Windows("3.xls").Activate

With ThisWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ThisWorkbook.Sheets("Sheet1")
.Protect "1234"
End With

Windows("73.xls").Activate

With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

End With
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
dim dim is offline
external usenet poster
  
Posts: 123
Default Specifying Cells To Copy Across Workbooks Not Working!
Hi again!

My name is Diarmaid and I'm from Ireland. :-)

Thanks for the advice so far. I tried those changes but have the same error.

Its Error 1004 - Application Defined or Object Defined Error.

I'm using Excel 2002, and the code is in 73.xls which is my user interface
workbook. 3.xls is just data storage, the data in which is saved in rows.

I simply want to be able to copy a complete row, from my data workbook, into
the user workbook. The only catch is that the row is specified by the number
in a cell in 73.xls, so its not always the same row being copied. The reason
for this then, is that the cell number is generated by a listbox where the
user chooses which employee's data they want to see.

Surely people often copy rows from one workbook to another using the number
in a cell?

Sheets("Calculations").Select
n = Range("A12").Value

Workbooks("3.xls").Activate

With ActiveWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ActiveWorkbook.Sheets("Sheet1")
.Protect "1234"
End With

Workbooks("73.xls").Activate

With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With



I changed one sheet around thats why it says 'calculations' now. The error
is the same.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default Specifying Cells To Copy Across Workbooks Not Working!

Your example still has unqualified references.
Note the dot before Cells.
Protecting the sheet empties the clipboard.
Protect it after pasting ...
'--
n = Sheets("Calculations").Range("A12").Value
With Workbooks("3.xls").Sheets("Sheet1")
.Unprotect "1234"
n = n + 1
.Rows(.Cells(n, n).Value).Copy
ThisWorkbook.Sheets("DataIn").Rows("3:3").PasteSpe cial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect "1234"
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"dim"
wrote in message
Hi again!

My name is Diarmaid and I'm from Ireland. :-)

Thanks for the advice so far. I tried those changes but have the same error.

Its Error 1004 - Application Defined or Object Defined Error.

I'm using Excel 2002, and the code is in 73.xls which is my user interface
workbook. 3.xls is just data storage, the data in which is saved in rows.

I simply want to be able to copy a complete row, from my data workbook, into
the user workbook. The only catch is that the row is specified by the number
in a cell in 73.xls, so its not always the same row being copied. The reason
for this then, is that the cell number is generated by a listbox where the
user chooses which employee's data they want to see.

Surely people often copy rows from one workbook to another using the number
in a cell?

Sheets("Calculations").Select
n = Range("A12").Value

Workbooks("3.xls").Activate

With ActiveWorkbook.Sheets("Sheet1")
.Unprotect "1234"
End With

Sheets("Sheet1").Select
n = n + 1
Rows(Cells(n, n).Value).Copy

With ActiveWorkbook.Sheets("Sheet1")
.Protect "1234"
End With

Workbooks("73.xls").Activate

With ThisWorkbook.Sheets("DataIn").Rows("3:3")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With



I changed one sheet around thats why it says 'calculations' now. The error
is the same.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
dim dim is offline
external usenet poster
  
Posts: 123
Default Specifying Cells To Copy Across Workbooks Not Working!
Thanks Jim, I pasted that section in but I still have the same error! ARGH!!!!
Its giving the same error on the same line! - .Rows(.Cells(n, n).Value).Copy

How can I finish this if I can't bring data from a storage workbook to my
other workbook??? .... sorry...venting over. This is executed upon a button
click.

Anyway, here is the entire code. I didn't post it all before because theres
a lot of random cells being cleared and stuff aside from the key aspect,
which is the code which is not working.

Sub DisplayEmp()
'
' Display Employee Macro - Imports employee data from 3.xls to 73.xls

Application.ScreenUpdating = False
Range("D9").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D11:F11").Select
Selection.ClearContents
Range("D12:F12").Select
Selection.ClearContents
Range("D14:F14").Select
Selection.ClearContents
Range("D15:F15").Select
Selection.ClearContents
Range("D16:F16").Select
Selection.ClearContents
Range("D17:F17").Select
Selection.ClearContents
Range("D18:F18").Select
Selection.ClearContents
Range("D20:F20").Select
Selection.ClearContents
Range("D22:F22").Select
Selection.ClearContents
Range("D24:F24").Select
Selection.ClearContents
Range("D26:F26").Select
Selection.ClearContents
Range("D28:F28").Select
Selection.ClearContents
Range("D30:F30").Select
Selection.ClearContents
Range("D32:F32").Select
Selection.ClearContents
Range("D34:F34").Select
Selection.ClearContents
Range("J6:L6").Select
Selection.ClearContents
Range("J8:L8").Select
Selection.ClearContents
Range("J10:L10").Select
Selection.ClearContents
Range("J12:L12").Select
Selection.ClearContents
Range("J14:L14").Select
Selection.ClearContents
Range("J15:L15").Select
Selection.ClearContents
Range("J16:L16").Select
Selection.ClearContents
Range("J17:L17").Select
Selection.ClearContents
Range("J18:L18").Select
Selection.ClearContents
Range("J20:K20").Select
Selection.ClearContents
Range("J22:K22").Select
Selection.ClearContents
Range("J24:K24").Select
Selection.ClearContents
Range("J26:K26").Select
Selection.ClearContents
Range("J28:K28").Select
Selection.ClearContents
Range("J30:K30").Select
Selection.ClearContents
Range("J32:K32").Select
Selection.ClearContents
Range("J34:K34").Select
Selection.ClearContents
Range("D9").Select

n = Sheets("Calculations").Range("A12").Value
With Workbooks("3.xls").Sheets("Sheet1")
.Unprotect "1234"
n = n + 1
.Rows(.Cells(n, n).Value).Copy
ThisWorkbook.Sheets("DataIn").Rows("3:3").PasteSpe cial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect "1234"
End With

Workbooks("73.xls").Activate
Sheets("DataIn").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D9").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D11:F11").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D12:F12").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D14:F14").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D15:F15").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("G3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D16:F16").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("H3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D17:F17").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("I3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D18:F18").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("J3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D20:F20").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D22:F22").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D24:F24").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("M3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D26:F26").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("N3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D28:F28").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("O3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D30:F30").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("P3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D32:F32").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("Q3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("D34:F34").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("R3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J6:L6").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("S3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J8:L8").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("T3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J10:L10").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("U3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J12:L12").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("V3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J14:L14").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("W3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J15:L15").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("X3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J16:L16").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("Y3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J17:L17").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("Z3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J18:L18").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AA3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J20:K20").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AB3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J22:K22").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AC3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J24:K24").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AD3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J26:K26").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AE3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J28:K28").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AF3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J30:K30").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AG3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J32:K32").Select
ActiveSheet.Paste
Sheets("DataIn").Select
Range("AH3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CurrentEmployees").Select
Range("J34:K34").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
dim dim is offline
external usenet poster
  
Posts: 123
Default Specifying Cells To Copy Across Workbooks Not Working!
This might help you to help me...I hope! :-)

If I change the offending line to: " .Rows(.Cells("n:n").Value).Copy ",
then I get a new error! It says Error 13 - Type mismatch error.

Does this help?


n = Sheets("Calculations").Range("A12").Value
With Workbooks("3.xls").Sheets("Sheet1")
.Unprotect "1234"
n = n + 1
.Rows(.Cells("n:n").Value).Copy
ThisWorkbook.Sheets("DataIn").Rows("3:3").PasteSpe cial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect "1234"
End With
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,290
Default Specifying Cells To Copy Across Workbooks Not Working!

The copy and paste part works for me.

Do you have any merged cells?
Is the sheet in ThisWorkbook protected?
Is the code in a standard module - not in a sheet module and
not in the ThisWorkbook module?

Run the code with the Msgbox in it, as shown below, then
look in Cells(n, n) and determine if the value is valid...

n = Sheets("Calculations").Range("A12").Value
With Workbooks("3.xls").Sheets("Sheet1")
.Unprotect "1234"
n = n + 1

Msgbox "n value is " & n

.Rows(.Cells(n, n).Value).Copy
ThisWorkbook.Sheets("DataIn").Rows("3:3").PasteSpe cial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect "1234"
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"dim"
wrote in message
Thanks Jim, I pasted that section in but I still have the same error! ARGH!!!!
Its giving the same error on the same line! - .Rows(.Cells(n, n).Value).Copy

How can I finish this if I can't bring data from a storage workbook to my
other workbook??? .... sorry...venting over. This is executed upon a button
click.

Anyway, here is the entire code. I didn't post it all before because theres
a lot of random cells being cleared and stuff aside from the key aspect,
which is the code which is not working.
-snip-

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid 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
Copy non-contiguous cells from all workbooks in folder dan dungan Excel Programming 8 November 14th 07 08:04 PM
Copy a range of cells to all workbooks in a folder? [email protected][_2_] Excel Programming 1 September 12th 07 07:06 PM
Can't copy data from cells between workbooks within the same excel snakeinbenson Excel Discussion (Misc queries) 7 March 20th 07 08:50 PM
Copy and pasting ref cells is not working Will Excel Discussion (Misc queries) 2 November 30th 06 10:07 PM
Copy cells from other workbooks kronos Excel Programming 1 April 22nd 04 04:57 PM


All times are GMT +1. The time now is 04:21 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement