Hi All
I am using the below code in the workbook before close function to open the backup file and paste data from the current workbook so that it doesn't get lost. It's openning the file fine, it's clearing what is currently in there fine, but the paste function is not working.
It seemed to be before today, but now just leaves me with an empty backup sheet - has anyone any ideas why it might not be working?
Please Login or Register to view this content.
If my answer helped pls click the star =)
If you are deleting what is in the backup then pasting the new information .. why not have the code save a copy as backup?
Maybe something like
Workbooks.SaveAs Filename:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"
Could potentially work, however would it replace existing files of that name without being prompted?
Hi cheeze83,
nope, the thing ImStevenB suggested wouldnt replace already existing file, you´d need to use kill command to remove previously created file and than save a new one.
btw. havent check on your code yet, just about to do it, so if i see some i´ll tell
Soul
first thing, you got typo in the dim part....wkbto vs wbkto (dim / set)
EDIT1: mby its just me but you got strange logic, you are copying things from the WB you´ve named TO and you´re copyíng them to list FROM, mby I´m just a bit off but its highly confusing to me
EDIT2: if you´re trying to select all its much easier, shorter, and cleaner to use this syntax:
rather than jumping end right & down (if it´s not all cells than sorry, it just seemed that it is).Please Login or Register to view this content.
rest seems OK,
So I believe all the problem is in the wrong declaration, than it should be working fine.
Soul
Last edited by SoulPrisoner; 11-18-2013 at 10:45 AM.
If you use
Application.DisplayAlerts = False
before the save .. it should write without the prompt SoulPrisoner
Hi Soul
Yeah - to be honest, it's slight laziness on my part - it's because earlier in the process (a lot earlier) the data is pulled FROM the backup - then into the current one (named To). So just so I didnt confuse myself, at the very end when it transfers it back (as the data can be updated) I just kept it the same - I didn;t notice the typo though - so thanks, I will go and see if that solves it....
Updated the spelling mistake and unfortunately doesn't solve it.
Another option:
Open any excel file and choose the "save as" option. When you are in the save as window, below the "File name" and "Save As Type" options, there is an option that reads "Tools" with a small drop down arrow next to it. Click on this drop down arrow and then from the dropdown list, choose "General Options".
In the window that pops up next, check the box that reads " Always create Backup"
Realize that this method will create a backup for all excel files.
A shorter version of your VBA follows: (untested)
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Cheers alansidman - just testing your code now.....
first, its kinda helpless if you give second half of a code which cant stand alone without the first partOriginally Posted by ImStevenB
and second, Have you tried what you´ve proposed?....it´s not working
Alunsidman - thanks for the code, unfortunately isn't working. When running I get:
Run-time error '438': Object doesn't support this property or method
Please Login or Register to view this content.
@Soul - yeah my oversight for not posting the first part .. but I had used that before and it worked for me.
My apologies
is the file represented by wbkFrom open when you run the code?
It's not no - the start of the code opens it
Hi again,
sorry didnt have time to come back to you earlier. So as it fails the paste as you said at the beginin, what exactly it does, i mean the code runs through without an error but there is no data copied?
prolly the selection is not working than, it selects empty cells - I´ve had this issue last week where this piece:
wasnt actualy selecting everything from top to bottom. Therefore I believe your try to select things to the right are not working....try to cut your selection code and make it stand alone, and test it on the file from which you´re copiing if the macro really selects the cells you intend....and than come back to use with your results.Please Login or Register to view this content.
best regards
Soul
Last edited by SoulPrisoner; 11-20-2013 at 06:27 AM. Reason: Typo
Hi Soul
No worries on the delay
I just tested on
on the before close - and it selects fine - going to run a little more of it and see if comes up with anything elsePlease Login or Register to view this content.
OK - so this is what I have put in now, and I've seen part of the problem - the code is:
However, it opens the workbook fine, but it is pasting into A1-A7 of the original sheetPlease Login or Register to view this content.
Workbooks.Open FileName:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"
Dim wkbTo As Workbook
Dim wbkFrom As Workbook
Set wbkTo = ThisWorkbook
Set wbkFrom = ActiveWorkbook
one question, are you trying to select all cells in the sheet or just a part of them and the rest of the sheet has to remain unchanged (cause if you dont mid to copy whole sheet, or rather should say content of it) use
instead of that long syntax with xldown or xlright....I believe that one would get the thing working straight awayPlease Login or Register to view this content.
EDIT1: btw. and the piece of the code I wanted you to test is this one:
and see if all you want to be selected is actualy selected with those markings around that you´re copiing it (cause I believe they are not).Please Login or Register to view this content.
Soul
hm, now reading your second post, so you´re telling me that you´re overwriting the things you want to remain in tact? well that is what I kinda said at the start that your variables confuses me and they might get mixed up in the process...:-D
Hey Soul
Yeah the long winded xldown are just there by default (as in I copied the code from another part of the process - it does a similar thing a little bit earlier, but just want a selected range). I did change it at one point to cells.select, but when the code wasn't working i just reverted it back.
At the moment, when I'm using the code it seems to be overwriting what is there with part of the VBA code itself - which is very confusing.
As for keeping things in tact - not particullary - would prefer to clear the sheet in the backup, then paste all the cells from the current sheet/workbook into that which is openened as part of the code, but its clearing it and pasting nothing.
Now looking at the code in parts - it seems that where it is pasting part of the vba into the cells on the opened workbook, it is then not selecting the required cells because of the code that is in there....
And this whole thing is starting to make me go boss-eyed lol
so you have tried to use cells.select and it didnt work either?...strange, let me try and write a piece that I´d use if i wanted to achieve what you´re talking here.
PS -- if you share a sample of the range you want to copy&paste I might replicate the code with some variables and asure of that the selection is proper; but as for now I´ll go with full sheet selection....;-)
Soul
yeah tried the whole cells.select, but didnt work - weirdest thing is until the other day, it worked fine - so I have NO idea what changed...
hey ho - cheers for your help Soul - appreciate your work on it, if your next bit doesnt work might just try something new completley....
Well I feel like there had to be some ninja patch from MS since something is not working for me as expected in excel aswell and dunno why but w/e.
Here is the code I´d use if I tried to achieve what you´ve described before (there is no need for the close of the main WB since that one is closing anyway cause you put that in the before close procedure). also I ´ve left out that msgbox u can put it there somewhere you like
it with the cells select, cause if you want me to get just a certain range I´d need to see it to define it somehow without using those xl movements.Please Login or Register to view this content.
Hope this one works (or I should say hope it works for you, cause it works for me if I just tried it on a simple case)
Soul
Cheers Soul
Getting a error message on this line
Error is Run-time error '9':Please Login or Register to view this content.
Subscript out of range
Hi again,
is the filename where you wanna back up rly that: "Backup.xlsx"....isnt it like just xls or xlsm or "back up" or some other typo?...Hm but it cant be cause otherwise you would get error earlier cause of the opening part....well, try it this way:
but it is rly strange cause it was working for me without any errorPlease Login or Register to view this content.
Soul
A little bit of playing around and seems to be there now...
Cheers for all your help on thisPlease Login or Register to view this content.
:-) yeah that one is what i proposed as the last solution that comes to my mind...;-) happy to hear it finaly works.
Anyway, if overwriting all cells becomes an issue, let me know we could make up a proper range selection....;-)
Best regards
Soul
PS -- dont forget to mark this thread as solved, and if you liked my approach pls consider the "Add reputation" button....;-)....thx
Cu around
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks