Not sure if it is refresh or workbook change code

ddnron

Board Regular
Joined
Oct 11, 2003
Messages
115
I want to thank anybody who helps me 1st. I'm a novice to code writing.

I have created a spreadsheet that has formulas in a few cells. There will be times when a user might insert data into the cell, that has the formula. When this change occurs they will print the spreadsheet data. I have created a print button, with code, to print a selected area.

After this happens I need to go back to the original spreadsheet, before they changed the cell (or cells), so that all the formulas are present again. The reason I want to go back is, when they import data, the formulas can read the new data. They have to import, then print the new data about ten times before closing the workbook.

I need the formulas to be there every time they import, some how I need the spreadsheet to be the orginal one they opened, after they changed a cell and printed the data. I would like to add code to the print button, where the code takes me back to the original spreadsheet, if possible.

Thanks again for any help.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Can't the user simply not save the workbook when they're done? That would allow the workbook to retain the formulas that may be overwritten...

Otherwise I would use a template for the original workbook, then any saving that needs to happen would create new workbooks, leaving the template intact.

-farnuckl
 
Upvote 0
I was trying to make it easier for the user, so they would not have to open the template 10 to 20 times, per day. The users will be importing data about 10 to 20 times, in a one hour period, per day.

I want to make it easy for them. Where they click the print button, it prints the data, resets the formulas, and then they can import the next file.

I thought there could be code that could do this.

I could have the code close the workbook, after it prints, but is there a code I could add, to the button, that would open this same file automatically.

Thanks for your help and suggestion.
 
Upvote 0
I am not aware of any way to close a workbook and continue to use code from it to say reopen it. Perhaps you could create a new workbook just to handle the opening and closing of the workbooks...
Or...
You could maybe save the orginal formulas somewhere else on the sheet, and write a little code to copy/paste them back after printing. That would circumvent the whole opening/closing problem.

-farnuckl
 
Upvote 0
I like your idea of copy and paste. I do have a backup sheet, that I have hidden. I could write code to copy this hidden sheet. Never have written code for copying and pasting cells, from another spreadsheet. Any code ideas would be helpful.

Thanks!
 
Upvote 0
Here are a couple snippets from some of my projects to get you on the right track:
This one is for same sheet copy/paste, where col & newcol are column references.
Code:
With Range(col & "26:" & col & "46")
        .Copy Destination:=Range(newcol & "26:" & newcol & "46")
        .Copy
        .PasteSpecial (xlPasteValues)
    End With

This one is for different sheet copy/paste, where goodnums is a range. Its a bit sloppy, I do know it is bad form to use select, but it doesn't seem to work correctly if I don't...
Code:
goodnums.Offset(0, 2).Copy
    Sheets("final").Range("c1").PasteSpecial Paste:=xlValues
    Sheets("import").Select
    goodnums.Offset(0, 1).Copy
    Sheets("final").Range("b1").PasteSpecial Paste:=xlValues
    Sheets("import").Select
    goodnums.Copy
    Sheets("final").Range("a1").PasteSpecial Paste:=xlValues
    Sheets("final").Select

Good luck.

-farnuckl
 
Upvote 0
Because I have a back up spreadsheet that is an exact copy, i used this code and it works fine. Thanks agian for your idea, suggestions and rapid help!

Sheets("danville").Range("p13:x24").Formula = Sheets("danville (2)").Range("p13:x24").Formula
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top