Macro to copy and save sheet

[spena129]

HI All,

I am having a little problem and need some help from the experts. I have a workbook that contains one tab for data and two additional tabs for reports. Within the report tabs are check boxes. I am trying to create a macro that would allow the user the option to save an individual report to a new workbook without the formula references to the previous "data" tab. Basically, copying and pasting Values and Number formats only. The problem I am encountering is that 1) I am too inept at VBA to write the macro myself (at the moment, but learning!) 2) when I try to record a macro to perform the process, the check boxes in the report and the original workbook all of a sardine show their default text, which need to be completely blank.

I have copied and pasted the code "recorded". Can anyone help? As you can see, I am just selecting the tab, copying it to a new workbook, copying the range of cells containing the report, and pasting as values and number formats. I know there is a more efficient way to do this....


Sub saveSF425()

'

' saveSF425 Macro

'


'

Sheets("SF425").Select

ActiveSheet.CheckBoxes.Add(569.25, 243, 24, 17.25).Select

ActiveSheet.CheckBoxes.Add(621, 242.25, 24, 17.25).Select

ActiveSheet.CheckBoxes.Add(484.5, 199.5, 24, 17.25).Select

ActiveSheet.CheckBoxes.Add(484.5, 213.75, 24, 17.25).Select

ActiveSheet.CheckBoxes.Add(484.5, 228, 24, 17.25).Select

ActiveSheet.CheckBoxes.Add(484.5, 243, 24, 17.25).Select

Sheets("SF425").Copy

Range("A1:L62").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _

xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

 

[Luke M]

Instead of copying and pasting, we should be able to just break the link to the original document. Assuming there's no other links to external workbooks, you can use this:

Sub saveSF425()


    Dim astrLinks As Variant

Sheets("SF425").Copy


    ' Define variable as an Excel link type.

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)


    ' Break the first link in the active workbook.

ActiveWorkbook.BreakLink Name:=astrLinks(1), Type:=xlLinkTypeExcelLinks


End Sub

 

[spena129]

Thanks, Luke. Worked like a charm. Any suggestions on good books to learn VBA?


Best,


Steve

 

[Luke M]

"Excel programming for dummies" by John Walkenbach is pretty good. (actually any of his books would be good.)

http://www.amazon.com/John-Walkenbach/e/B000APG96Y/ref=ntt_athr_dp_pel_pop_1


And of course, a shout-out to Chandoo who recently started some VBA classes.

Myself, I recommend doing what you were doing...experimenting with recording yourself, tinkering with the code, and checking the forums for advice.


the Answers forum is pretty good. Don't be afraid to search different topics to learn new tricks. =)

http://answers.microsoft.com/en-us/office/forum/excel?page=1&tab=no&sort=LastReplyDate&dir=Desc&tm=1299595310435

 

[spena129]

I know this is an old post, but I have a follow up question. Can someone let me know how to just copy/paste a range (values and formats) to a new workbook without links? I am getting a VBA run-time error 1004 50% of the time/

 

[Luke M]

Example:

ActiveSheet.Range("A1:A3").Copy Workbooks("Book1").Worksheets("Sheet1").Range("B2")


The key is remembering to state the full address (workbook.sheet.range) so that the VB knows exactly where you're trying to get to.