Open new workbook, copy and paste not working

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

If you use

Application.DisplayAlerts = False

before the save .. it should write without the prompt SoulPrisoner

Originally Posted by ImStevenB

If you use

Application.DisplayAlerts = False

before the save .. it should write without the prompt SoulPrisoner

first, its kinda helpless if you give second half of a code which cant stand alone without the first part

and second, Have you tried what you´ve proposed?....it´s not working

@Soul - yeah my oversight for not posting the first part .. but I had used that before and it worked for me.

My apologies

OK - so this is what I have put in now, and I've seen part of the problem - the code is:

Workbooks.Open FileName:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"

Dim wbkTo As Workbook
Dim wbkFrom As Workbook

Set wbkTo = ThisWorkbook
Set wbkFrom = ActiveWorkbook

wbkFrom.Activate
Cells.Select
Selection.ClearContents

wbkTo.Activate

Sheets("Payment Capture").Visible = True
Sheets("Payment Capture").Select
Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

However, it opens the workbook fine, but it is pasting into A1-A7 of the original sheet

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

cells.select

instead of that long syntax with xldown or xlright....I believe that one would get the thing working straight away

EDIT1: btw. and the piece of the code I wanted you to test is this one:

wbkTo.Activate
Sheets("Payment Capture").Select
Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

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

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

Hi Soul

No worries on the delay

I just tested on

Sheets("Payment Capture").Visible = True
Sheets("Payment Capture").Select
Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

on the before close - and it selects fine - going to run a little more of it and see if comes up with anything else

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

Sub Cheeze83()

Dim OrgFile As String
Dim TargFile As String

OrgFile = ThisWorkbook.Name
TargFile = "Backup.xlsx"

Workbooks.Open Filename:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"

Windows(OrgFile).Activate
Sheets("Payment Capture").Visible = True
Sheets("Payment Capture").Select
Cells.Select
Selection.Copy

Windows(TargFile).Activate
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

Windows(OrgFile).Activate
Sheets("Payment Capture").Visible = False
Sheets("PARF System").Select
ActiveWorkbook.Save

End Sub

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.

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

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

Cheers Soul

Getting a error message on this line

Windows(TargFile).Activate

Error is Run-time error '9':

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:

Sub Cheeze83()

Dim OrgFile As String
Dim TargFile As String

OrgFile = ThisWorkbook.Name

Workbooks.Open Filename:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"
TargFile = ActiveWorkbook.Name

Windows(OrgFile).Activate
Sheets("Payment Capture").Visible = True
Sheets("Payment Capture").Select
Cells.Select
Selection.Copy

Windows(TargFile).Activate
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

Windows(OrgFile).Activate
Sheets("Payment Capture").Visible = False
Sheets("PARF System").Select
ActiveWorkbook.Save

End Sub

but it is rly strange cause it was working for me without any error

Soul

A little bit of playing around and seems to be there now...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim OrgFile As Workbook
Dim TargFile As Workbook

Set OrgFile = ThisWorkbook

Workbooks.Open FileName:=ThisWorkbook.Path & "\Payee Backup\Backup.xlsx"
Set TargFile = ActiveWorkbook

OrgFile.Activate
Sheets("Payment Capture").Visible = True
Sheets("Payment Capture").Select
Cells.Select
Selection.Copy

TargFile.Activate
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

OrgFile.Activate
Sheets("Payment Capture").Visible = False
Sheets("PARF System").Select
ActiveWorkbook.Save

End Sub

Cheers for all your help on this

:-) 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