Using Control Button to Roll Over Values to New Worksheet

[jksb]

I would like to create a "SaveAs" (or whatever you suggest) control button that will 1). create a new worksheet in a folder *and* 2). remove values from columns 1&2; 3). replace values from columns 5&6 to 3&4, 4). and remove values from columns 5&6. I know how to do it manually (i.e.linking & formulas0; but my boss wanted to know if it can be done in one click of a button . Is this possible in Excel?

Thanks!!!

Jackie

 

[Dreamboat]

Yes, jk. This can be as simple as using a new, blank workbook and save it as MyMacro.xls or something like that.

Then record the steps you want taken, i.e.:

Open filename.xls
File-Save as and give it new name (this will ALWAYS be the same name so it will overwrite yesterday's copy...)
Move the values around, save it, close it.

Stop recording.

Before you do anything, be sure to copy your real files to another location. But you DO need to work with files that have the correct names.

When you're done, test your macro by copying your original back into this folder and running the macro that is in MyMacro.xls.

See if you can do all that, I'll let you know how to proceed to save this file as a personal.xls file (or you can check help, or others will know how to do that).
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[jksb]

Thanks...got my cup of hot chocolate laced with coffee, opened up Help, and will tackle it . This is my first VBA, and I know that once I do one, it will be simple, but the first one around always goes over my head for awhile

Will be back at you, I'm sure

Jackie

 

[jksb]

Now that was embarrassingly easy . My only question that each workbook is linked to the previous one (to carry running totals forward) and what is happening is that when I make a new one, it doesn't update the link (i.e., it looks for data in the original workbook). In other words, when I make a new workbook (new.xls) I change the name to (workbookthree.xls) I want the beginning total link to go to workbooktwo.xls, not workbooknew.xls, to populate the first field (startinventory). How can I get this to do it automatically? (I hope I'm making sense

Thanks!
Jackie

 

[jksb]

Okay, I'm down to this one thing. This is what I have in the macro to go to the previous workbook and populate the columns:

Range("H5&quot.Select
ActiveCell.FormulaR1C1 = "=[Jan.xls]Week4!R5C10"
ActiveCell.FormulaR1C1 = "=[Jan.xls]Week4!RC[2]"

What I would like it to do is to go to the workbook I just SavedAs from and poplulate from there. Can someone tell me how I tell it to go to the correct.xls workbook (the one I created the new workbook from)? I assume it's the Jan.xls that will need to be changed, but I don't know if there is already a code to add there that's already been created. Thanks! Jackie