PDA

View Full Version : Solved: Copy between sheets



Dowsey1977
06-01-2006, 05:04 AM
Hi,I am trying to copy information from one worksheet to another, and the code I would normally use doesn't seem to be working. I've broken the code down to see where it is going wrong, and I'm down to the bare minimum. This is how my code looks:Sub Transfer()Sheets("FundsOutList").SelectRange("A30").End(xlUp).Offset(1, 0).SelectEnd Sub
But when I run the macro a messagebox appears that just says '400'!!Any ideas what's going on?

Edit by Lucas: I added vba tags to your code for easier reading

OBP
06-01-2006, 05:11 AM
Hi, it is difficullt to ascertain what is wrong, can you post a copy of your Workbook, with some dummy data if necessary so we can see what is going wrong?

Dowsey1977
06-01-2006, 05:22 AM
For some reason what I assume is the attach file button isn't working! Can't even press it

OBP
06-01-2006, 05:29 AM
At the bottom left corner of this page what does it say your posting rules are?
Do you want to email me a copy, if so I will send my email address in a private mail.

Dowsey1977
06-01-2006, 05:31 AM
You may post new threadsYou may post repliesYou may post attachmentsYou may edit your postsvB code is OnSmilies are On[IMG] code is OnHTML code is Off

OBP
06-01-2006, 05:33 AM
That says you shouldn't have a problem then, very odd.
What about emailing?

lucas
06-01-2006, 05:33 AM
Click on "Post Reply" on the left side of the last post....after typing your post scroll the page down and look for the button that says "Manage Attachments"

Dowsey1977
06-01-2006, 05:38 AM
Nope...there is nothing that says "Manage Attachments" after clicking Post Reply.Under additional options it has a section just called "Attach Files", but only says the valid file extensions.I can email though. Tried doing a PM, but couldn't attach anything there either.

OBP
06-01-2006, 05:43 AM
Directly under where you write your post there is a "Go Advanced" button, did you click that?

Dowsey1977
06-01-2006, 05:47 AM
Yep...still nothing

OBP
06-01-2006, 05:48 AM
OK, I will private mail you.

lucas
06-01-2006, 05:52 AM
If you can get with OBP and email it to him, maybe he can post it for you and I will bring your problem to the attention of an admin and see if your permissions are set wrong......

Dowsey1977
06-01-2006, 05:52 AM
Great...thanks all! I have just emailed it to OBP

OBP
06-01-2006, 06:01 AM
Ok, the first thing is that you do not have any error handling routine to tell you what the error is. I will dig a bit deeper and post it back on here

Dowsey1977
06-01-2006, 06:03 AM
Excellent, thanks

OBP
06-01-2006, 06:19 AM
The failure is in trying to select the various ranges, the VBA does not like the way that you are trying to do it. I think I will have to break down your current range statements to see what is triggering the error.

OBP
06-01-2006, 06:25 AM
Right in the "Test" macro you are not selecting the "Lists" sheets before trying to select the range. Use this
Sheets("Lists").Select
Range("A65000").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select

Dowsey1977
06-01-2006, 06:26 AM
I tried running just 1 line of code on the "FundsOutList" sheet that said Range("A30").Select and it didn't like it. Try running it on the "FundsOut" sheet and it works fine

Dowsey1977
06-01-2006, 06:28 AM
Sorry...I am running the "Transfer" macro which is what is linked to the button on the "FundsOut" sheet.

OBP
06-01-2006, 06:30 AM
Same on the Named range macro.

OBP
06-01-2006, 06:33 AM
Sorry I haven't got a button on the FundsOut sheet.

Dowsey1977
06-01-2006, 06:34 AM
Oh...ok, well the "Transfer" macro is the macro I'm trying to get to work. The other weren't being used so deleted them.

OBP
06-01-2006, 06:42 AM
It has the same problem of not liking the range selection. The wierd thing is it is the same as the ones in "Test" and Rangename which are working OK now.

OBP
06-01-2006, 06:49 AM
Ok, I have got it, it is one of those wierd Excel bugs where code sometimes works and sometimes doesn't. To make it work use this
ActiveSheet.Range("a65000").End(xlUp).Select

OBP
06-01-2006, 06:57 AM
For those that haven't already got the error routine to help debugging macros and VB here is an example.

On Error GoTo Err_test ' this line goes at the beginning of the code


Exit_test: ' this section goes at the end of the code before the "End Sub"
Exit Sub

Err_test:
MsgBox Err.Description
Resume Exit_test

replace the word "test" with whatever your Sub Procedure is called.

Dowsey1977
06-01-2006, 08:14 AM
Just to let you all know that this thread has been solved! Thanks for all the help!

johnske
06-01-2006, 08:47 AM
Nope...there is nothing that says "Manage Attachments" after clicking Post Reply.Under additional options it has a section just called "Attach Files", but only says the valid file extensions.I can email though. Tried doing a PM, but couldn't attach anything there either.Hi Dowsey,

For future reference, if you got to the section that gives the valid file extensions you were almost there...

A lot of people have trouble with attachments the very first time so I've stuck a thread here (http://www.vbaexpress.com/forum/showthread.php?p=65493#post65493) that addresses the issue. There are two attachments to view, they are here (part1) (http://www.vbaexpress.com/forum/attachment.php?attachmentid=3227&d=1149175117) and here (part2) (http://www.vbaexpress.com/forum/attachment.php?attachmentid=3228&d=1149175625)

Regards,
John :)

Dowsey1977
06-02-2006, 01:02 AM
Thanks for the information re. attachments, however, under "Attach Files" I do not have the button that is shown in your diagram available, all I have is 1 line that says "Valid file extensions: bmp doc gif jpeg jpg pdf png xls zip". Ai also don't seem to have the "Thread Management" section either. And it seems weird that I cannot mark the thread solved. I have been able to do these in the past, but I seem to have lost that functionality.

johnske
06-02-2006, 01:25 AM
I'll let Jake know, may be something to do with the forum upgrades :)

Jacob Hilderbrand
06-02-2006, 11:26 AM
What browser are you using? Also if you could take a screenshot of what you see and email it to me, that would help.

Thanks