Specifying Cells To Copy Across Workbooks Not Working!

D

dim

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
 
J

JLGWhiz

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
 
J

Jim Cone

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
 
D

dim

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.
 
J

Jim Cone

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").PasteSpecial _
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.
 
D

dim

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").PasteSpecial _
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
 
D

dim

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").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Protect "1234"
End With
 
J

Jim Cone

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").PasteSpecial _
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-
 
D

dim

Hi Jim, I don't understand that its working for you!

I have no merged cells in the row being copied to/from, or the list number
cell.
The sheet is not protected.
The code is in a standard module, Module 4.

After running with that code the value was valid, in my case '6' and I
received the 1004 error again. The list number cell is formatted as 'Number'.
 
J

Jim Cone

Sorry, but I am out of ideas.
Jim Cone



"dim"
wrote in message
Hi Jim, I don't understand that its working for you!

I have no merged cells in the row being copied to/from, or the list number
cell.
The sheet is not protected.
The code is in a standard module, Module 4.

After running with that code the value was valid, in my case '6' and I
received the 1004 error again. The list number cell is formatted as 'Number'.
 
D

dim

No problem Jim,

Thankyou very much for all the help. I have to say I'm a bit miffed that
it's working for you...thats just confusing me more! lol :)

I guess I'll do it the hard way...I know this way will work but I thought
there must have been a better way. I'll write out a Select Case with 3000 or
so "Case Is = " to cover the selection of any of the list items. Some of my
lists have up to 3000 items, others have about a hundred. It'll take a while,
but if I had started last week instead of trying to find an easy way, I'd
have some done by now! Haha! Such is life. :)

If anyone figures this out I'd appreciate a heads up. L8rs. Thanks again.
 
J

Jim Cone

Re: "I'll write out a Select Case with 3000 or so Case Is = "
DON'T DO THAT - find another way!

Re: "I don't understand that its working for you!"
I made up a couple of test workbooks using your workbook and sheet names.
I assigned a value of 6 to "n"
I ran the code.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"dim"
wrote in message
No problem Jim,
Thankyou very much for all the help. I have to say I'm a bit miffed that
it's working for you...thats just confusing me more! lol :)

I guess I'll do it the hard way...I know this way will work but I thought
there must have been a better way. I'll write out a Select Case with 3000 or
so "Case Is = " to cover the selection of any of the list items. Some of my
lists have up to 3000 items, others have about a hundred. It'll take a while,
but if I had started last week instead of trying to find an easy way, I'd
have some done by now! Haha! Such is life. :)
If anyone figures this out I'd appreciate a heads up. L8rs. Thanks again.
 

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

Need Help with a VBA subroutine 0
Specifying Row to Copy Across ? 5
Macro to delete row if cells greater than certain value 1
Unhide sheet, copy and rename new sheets from list, rehide sheet 8
Need help to fix vlookup function in macro 1
Code does not error but no copy either 4
Open four Wbooks, copy four columns from each to Master Wbook 16
Copy FROM - TO Object/Block variable not set 6

Top