Save a selected range as script

sambuddy

New Member
Joined
Oct 16, 2019
Messages
2
it is obvious I do not know what I am doing. What I am trying to accomplish is by pressing the button, Range A1:A12 on sheet called DATA_1 which I set it to be as "ssheet2" be saved on desktop as DATA_1.scr

but executing this macro will change my workbook name to DATA_1. Could anyone please help?

Also how can I ask the saved script file to be on any user desktop and not particular to mine, so I could share the excel file to different users and by executing the macro, the DATA_1.scr generates on their desktop. is there a generic way to formulate that?

Also is there a way to overwrite if there are existing files with the same name on anyone`s desktop without asking any question - if exists just overwrite.
thanks

Private Sub bt2_Click() Set ssheet2 = ThisWorkbook.Sheets("DATA_1")
Range("A1:A12").Select
ssheet2.SaveAs Filename:="C:\Users\sambuddy\Desktop\DATA_1.scr", _
FileFormat:=xlUnicodeText, CreateBackup:=False


End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Watch MrExcel Video

Forum statistics

Threads
1,089,742
Messages
5,410,169
Members
403,302
Latest member
Richard Genet

This Week's Hot Topics

Top