VBA Code Issues

[mtb2434]

Hi,

I'm very new to VBA and trying to experiment with new macros. I have recorded a macro and saved into my Personal.xlsb workbook because I would like to use on many worksheets in the future. This code below is to save all my worksheets in the workbook as new files. It works when I have the exact module in a separate workbook however when I try to run the macro for another workbook through Personal.xlsb, I get an out of stack space error. Any help you guys have would be much appreciated. Thank you



Sub SaveSheets()
'
' SaveSheets Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Application.Run "PERSONAL.XLSB!SaveSheets"
Dim strPath As String
Dim ws As Worksheet


Application.ScreenUpdating = False


strPath = ActiveWorkbook.Path & "\"
For Each ws In ThisWorkbook.Sheets
ws.Copy
'Use this line if you want to break any links:
BreakLinks Workbooks(Workbooks.Count)
Workbooks(Workbooks.Count).Close True, strPath & ws.Name & ".xlsx"
Next


Application.ScreenUpdating = True
End Sub


Sub BreakLinks(wb As Workbook)
Dim lnk As Variant
For Each lnk In wb.LinkSources(xlExcelLinks)
wb.BreakLink lnk, xlLinkTypeExcelLinks
Next
End Sub

 

[Kamolga]

Hi,

I also had a code that would work when run from its own file but not from personal workbook.

What I did to make sure Excel was understanding what it had to start with was adding

ActiveWorkbook.ActiveSheet.Cells.Select


to the first line of the macro (in personal workbook), and now it works in every files.

 

[mtb2434]

Unfortunately, I still get the out of stack space error when doing that. I believe it has to do with the Application.Run line but not really sure what to do about it. I appreciate your help

Sub SaveSheets()
ActiveWorkbook.ActiveSheet.Cells.Select
'
' SaveSheets Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Application.Run "PERSONAL.XLSB!SaveSheets"
Dim strPath As String
Dim ws As Worksheet




Application.ScreenUpdating = False




strPath = ActiveWorkbook.Path & "\"
For Each ws In ThisWorkbook.Sheets
ws.Copy
'Use this line if you want to break any links:
BreakLinks Workbooks(Workbooks.Count)
Workbooks(Workbooks.Count).Close True, strPath & ws.Name & ".xlsx"
Next




Application.ScreenUpdating = True
End Sub




Sub BreakLinks(wb As Workbook)
Dim lnk As Variant
For Each lnk In wb.LinkSources(xlExcelLinks)
wb.BreakLink lnk, xlLinkTypeExcelLinks
Next
End Sub

 

[shg]

Sub SaveSheets()
 '
 ' SaveSheets Macro
 '
 ' Keyboard Shortcut: Ctrl+Shift+S
 '
 Application.Run "PERSONAL.XLSB!SaveSheets"

You run the sub, and the first line of code calls itself, which calls itself, which calls itself, ...

Why is that line there?

 

[mtb2434]

I believe that came from recording my macro in the original workbook. But if it is continuously calling itself that is probably why I run out of stack space. How would I best adjust that?

 

[shg]

Remove that line.

 

[jolivanes]

It looks like you might want to look into the difference between "ActiveWorkbook" and "ThisWorkbook" also.
ThisWorkbook vs ActiveWorkbook » Bacon Bits

 

[mtb2434]

Thanks guys! Looks like removing that line of code and adjusting code to "ThisWorkbook" cleared everything up.

 

[briansabourin]

Hello! I'm new to Mr. Excel and new to using Macro....
I have a problem with recording a Macro.
Each time I try to record one ( irecord it in My Personel exel sheet) it all works, but when I try to use it in another sheet I always get the same bug.

Keyboard Shortcut: Ctrl+s
'
Sheets("Sheet1").Select
Columns("F:L").Select

As you can see my macro is going to 'sheet 1' but it is not looking for sheet 1 in 'my personal excel' I think...

What do I do to correct this?

Thanks in advance!

Brian

 

[Kamolga]

Hi,

Welcome to the forum.

Hello! I'm new to Mr. Excel and new to using Macro....
I have a problem with recording a Macro.
Each time I try to record one ( irecord it in My Personel exel sheet) it all works, but when I try to use it in another sheet I always get the same bug.

Keyboard Shortcut: Ctrl+s
'
Sheets("Sheet1").Select
Columns("F:L").Select

As you can see my macro is going to 'sheet 1' but it is not looking for sheet 1 in 'my personal excel' I think...

What do I do to correct this?

Thanks in advance!

Brian

If you have nothing else previously in your code, you can delete the row

Sheets("Sheet1").Select

and your code will work on any active sheet.

Otherwise you can refer to your active sheet as "activesheet.select" (but this does not really make sense).