Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Workbooks.Open Filename:="C:\Ex

Status
Not open for further replies.
jaydeebe

jaydeebe

IS-IT--Management
Nov 14, 2003
169
GB
Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Columns("A:D").Select
Selection.Copy
Windows("testing4.xls").Activate
Columns("A:D").Select
ActiveSheet.Paste

runtime error1004 select range method of class failed

I'M SURE THIS USED TO WORK BUT NOT ANYMORE. i HAVE IT SO THAT YOU CLICK A BUTTON AND IT OPENS AND COPIES DATA FROM A-D COLUMNS IN ONE FILE TO THE OTHER WHERE THE PROGRAMED BUTTON IS.
 
Sort by date Sort by votes
Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Range("A:D").Select
Selection.Copy
Windows("testing4.xls").Activate
Range("A:D").Select
ActiveSheet.Paste

or:
Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Range("A:D").Copy destination:=workbooks("testing4.xls").activesheet.range("A:D")


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
try this variation...

Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Columns("A:D").Select
Selection.Copy
Windows("testing4.xls").Activate
cells(1,1).Select
ActiveSheet.Paste
 
Upvote 0 Downvote
Is your activesheet in safeacc.xls a worksheet? Is your other workbook still called testing4.xls? Where do you get the error?

By the way, you can replace

Columns("A:D").Select
Selection.Copy
Windows("testing4.xls").Activate
Columns("A:D").Select
ActiveSheet.Paste

with

columns("A:D").copy workbooks("testing4.xls").range("A1")



Rob
[flowerface]
 
Upvote 0 Downvote
Geoff,
In my version of Excel (2000), the columns format works fine (but range works too).


Rob
[flowerface]
 
Upvote 0 Downvote
The code is triggered by a button in testing4.xls, it appears to open the file safeacc.xls and select the columns but won't copy the data to testing.xls.
 
Upvote 0 Downvote
Jay,
XL2000 vs XL97 won't make a difference, for the most part. You still need to provide more information (see my questions above, for example) if you want targeted help.


Rob
[flowerface]
 
Upvote 0 Downvote
The error is on the first line

Columns("A:D").Select

the active sheet is the testing.xls sheet 1 because that is where i clik abutton to trigger this code.
 
Upvote 0 Downvote
jaydeebe
Just to expand on Rob is saying - or maybe clarify. Once your second workbook is opened testing.xls "sheet1" is no longer the active sheet. Is the active sheet in safeacc.xls actually a worksheet?

You also say the error occurs at "Columns("A:D").Select"
Is that the first select (for the copy) or the second (for the paste)? If it's the second one you can ignore my comments above!!

;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Upvote 0 Downvote
Think we may be in the wrong direction here. Jaydeebe - if you are using a controls toolbox button to run this code, make sure the "TakeFocusOnClick" property is set to false - that's where my money is....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
xlbo - i tried changing th taake focus on click setting and it didn't make any difference.

loomah - the first select is highlighted, but it does open the file and select the columns
 
Upvote 0 Downvote
Well, as long as there is a workbook called safeacc.xls, I can run the code thru fine - no probs - there must be something in the workbook that is preventing the select - what happens if you use:
Workbooks.Open Filename:="C:\Export\Safeacc.xls"
sheets("Sheetname").select
Columns("A:D").Select
Selection.Copy
Windows("testing4.xls").Activate
Columns("A:D").Select
ActiveSheet.Paste

where sheetname is the name of the sheet with data that you want to copy

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
Upvote 0 Downvote
Hi there!

Try Range("A:D").Select, cause Row and Column work with numbers, e.g. (Columns(1).Select) with standard settings, whereas Range always works with A1 notation, don't they? ;-)

MakeItSO

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
 
Upvote 0 Downvote
Your code gives me the same error as per original post (even if I change columns for range)

------------------------------------------------------------

Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Range("A:D").Copy
ActiveWindow.Close
Range("A:D").Select
ActiveSheet.Paste

I also tried the above code instead and this now runs without error msg, but does not do the paste.
 
Upvote 0 Downvote
I'm quite confused now so I'm going to lie down. But before I go...

The workbook opens ok? - yes
The columns are selected? - yes
An error occurs on the select line of code that appears to have just worked? Hence the confusion!

Is the code actually selecting the columns or were they already selected when the book was opened ie the book was saved with those columns already selected?

Is the correct worksheet selected in safeacc.xls when it's opened? Again, is it actually a worksheet?

Did you try Rob's suggestion for shorter code a few posts back? If not try this substitution to see what happens:-

replace
Columns("A:D").Select
Selection.Copy
with
Columns("A:D").Copy

;-)


If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Upvote 0 Downvote
Now that clears it up:
You must not close the window, since this will empty the clipboard. Just activate the other window:
Range("A:D").Copy
Windows.Item(your window).Activate
Range("A:D").Select
ActiveSheet.Paste

;-)
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:
 
Upvote 0 Downvote
That's got to be some sort of record - 3 posts appear before mine!!!!! Including replies. Oh the joys of tin can and string internet connections.

Good point from Andreas. At what point did the close window code appear?
;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Upvote 0 Downvote
You're correct when you say that i had saved the sheet with the columns selected.

ok, it seems that the data is not being copied from the opening file (safeacc.xls). I can run the code and then do a manual paste but the data pasted is from the testing.xls not safeacc.xls as required.

The problem therefore seems to be with the activation, select and copy from safeacc.xls.

loomah - the close window code was something i tried, i have removed it again.
 
Upvote 0 Downvote
OK, lets try to put this to bed

You have one file, testing.xls, with a button in it. The TakeFocusOnClick property of the button is set to false.

Clicking the button should do the following
Open safeacc.xls
Select columns A - D (no matter what sheet is active when the book is opened)
Copy these columns
Activate testing.xls
Select columns A - D (this will be on the sheet with the button)
Paste

And it's not working?

Utilising everything that has been suggested to you so far try this.

Code: 
Sub copyData()
Dim wbSafe As Workbook, wbMe As Workbook
Set wbMe = ThisWorkbook 'this workbook is testing.xls
Set wbSafe = Workbooks.Open(Filename:="C:\Export\Safeacc.xls")
wbSafe.Worksheets("PUT_YOUR_SHEETNAME_HERE").Columns("A:D").Copy _
    Destination:=wbMe.Worksheets("DESTINATION_SHEET_NAME").Range("A1")
wbSafe.Close True   'close if required
Set wbSafe = Nothing
setwbme = Nothing
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

kapok1
  • Locked
  • Question
how to use vba to forbid "Worksheet Menu Bar" being selected
Replies
6
Views
441
kapok1
kapok1
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
NewbieMacro
  • Locked
  • Question
Hello, I have a sheet named "Fin
Replies
2
Views
166
NewbieMacro
NewbieMacro
remeng
  • Locked
  • Question
Run PowerShell Commands from Excel VBA
Replies
14
Views
3K
dhookom
dhookom
linuxguyEastCoast
  • Locked
  • Question
export text from non-public docx, pptx, pdf
Replies
0
Views
454
linuxguyEastCoast
linuxguyEastCoast

Part and Inventory Search

Sponsor

Back
Top