Closing BA and forgetting to save Excel worksheet
1. No need to open Bet Angel
2. Open your BA workbook
3. Select file, save as, and save as “BA Copy”
4. From the copy, select file/open and re-open BA book
5. Temporarily unhide all columns
6. Select rows 1:60 (should be enough), and columns A:K (Don’t go beyond K)
7. Back to the copy, range A1, paste special, paste link ( button is right in bottom left hand corner)
8. You should see a lot of 0’s appear, these can be hidden (options, view, hide zero values, if I remember), or you can leave as is. Appearance is not paramount here.
9. While we’re still with the copy, I strongly suggest that you fill rows 7 & 8 with a bright red colour, this will distinguish both copies)
10. Save and close the copy
11. With the BA book, press Alt F11 to open the vba editor, and choose “This Workbook” on the left hand side, and copy and paste this in:
Public CurrentAD As String, PrevAD As String, CurrentR As Range, PrevR As Range
Private Sub Workbook_Open()
Sheets("Bet Angel").Range("A1").Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim ShtName As String
Dim TForm As String
ShtName = ActiveSheet.Name
If ActiveSheet.Name = "Bet Angel" And Target.Column < 12 Then
Exit Sub
End If
On Error GoTo NoBook
CurrentAD = ActiveCell.Address
Set CurrentR = ActiveCell
If PrevAD = "" Then
PrevAD = CurrentAD
Set PrevR = CurrentR
End If
TForm = Range(PrevAD).Formula
Workbooks("BA Copy").Sheets(ShtName).Range(PrevAD) = TForm
PrevAD = CurrentAD
Exit Sub
NoBook:
MsgBox " Please open BA Copy"
End Sub
Close BA book down, re-open and test it out. Anything you enter will be reproduced in the copy.
On the BA sheet, anything from column L onwards and anything on any of the other two sheets.
Don’t forget, as it’s in as a “worksheet selection change” event, anything you do type won’t be copied over until you press enter.
Note: The sheet names in the copy should be the same as the original and also, it doesn’t deal with ranges. i.e If you select a range and copy/paste, only the first cell will be copied.
Not the most elegant of solutions but, if you exit Bet Angel , this will still be there asking if you want to save it, so your original work is not lost.
Nigel
2. Open your BA workbook
3. Select file, save as, and save as “BA Copy”
4. From the copy, select file/open and re-open BA book
5. Temporarily unhide all columns
6. Select rows 1:60 (should be enough), and columns A:K (Don’t go beyond K)
7. Back to the copy, range A1, paste special, paste link ( button is right in bottom left hand corner)
8. You should see a lot of 0’s appear, these can be hidden (options, view, hide zero values, if I remember), or you can leave as is. Appearance is not paramount here.
9. While we’re still with the copy, I strongly suggest that you fill rows 7 & 8 with a bright red colour, this will distinguish both copies)
10. Save and close the copy
11. With the BA book, press Alt F11 to open the vba editor, and choose “This Workbook” on the left hand side, and copy and paste this in:
Public CurrentAD As String, PrevAD As String, CurrentR As Range, PrevR As Range
Private Sub Workbook_Open()
Sheets("Bet Angel").Range("A1").Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim ShtName As String
Dim TForm As String
ShtName = ActiveSheet.Name
If ActiveSheet.Name = "Bet Angel" And Target.Column < 12 Then
Exit Sub
End If
On Error GoTo NoBook
CurrentAD = ActiveCell.Address
Set CurrentR = ActiveCell
If PrevAD = "" Then
PrevAD = CurrentAD
Set PrevR = CurrentR
End If
TForm = Range(PrevAD).Formula
Workbooks("BA Copy").Sheets(ShtName).Range(PrevAD) = TForm
PrevAD = CurrentAD
Exit Sub
NoBook:
MsgBox " Please open BA Copy"
End Sub
Close BA book down, re-open and test it out. Anything you enter will be reproduced in the copy.
On the BA sheet, anything from column L onwards and anything on any of the other two sheets.
Don’t forget, as it’s in as a “worksheet selection change” event, anything you do type won’t be copied over until you press enter.
Note: The sheet names in the copy should be the same as the original and also, it doesn’t deal with ranges. i.e If you select a range and copy/paste, only the first cell will be copied.
Not the most elegant of solutions but, if you exit Bet Angel , this will still be there asking if you want to save it, so your original work is not lost.
Nigel
You do not have the required permissions to view the files attached to this post.