code fails in Share WorkBook

[sgmpatnaik]

Hi


i got this code from the google search, kindly advise me about this code why the code is working in normal mode and not in Workbook Share Mode


and correct the code for work in normal and in Workbook Share mode also

[pre]

Sub NewCopy()

Dim strFileName As String
Dim range1 As Range
Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("Sheet1").Activate
Set rng1 = Range("A1:E7")
Set rng2 = Range("A80:E86")
Set myMultiRanges = Union(rng1, rng2)
myMultiRanges.Select

strFileName = InputBox("Type a name for the new workbok", "File Name")
If Trim(strFileName) = vbNullString Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Selection.Copy
Sheets.Add.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveSheet.UsedRange.EntireColumn.AutoFit
ActiveSheet.Move
ActiveWorkbook.SaveAs "C:UsersDesktop" & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

[/pre]

Thanks


SP

 

[DaveTurton]

You can not use any macros on a shared workbook

 

[sgmpatnaik]

@DaveTurton


Hi


Thanks for Quick replay, but when we remove one line that is


ActiveSheet.Move


then the macro is worked in Shared Workbook Also but there is one problem that is it's copy all the worksheets in the workbook


Lets C what type of replay is coming from our Ninja's


Thanks


SP

 

[DaveTurton]

You've not told it where to move the sheet to have you?


i.e.

ActiveSheet.Move after:=Worksheets(Worksheets.Count)

 

[sgmpatnaik]

@Dave


Hi I think it's my mistake sorry,


i want to copy the given range and paste in the new file which is specified in the InputBox File Name


Thanks


SP

 

[sgmpatnaik]

@DaveTurton


Hi


if i change that line as your suggested then it's work in Shared workbook but it's copy the entire worksheets from the workbook but my thinking is i want to copy the specified Range to another workbook which workbook name is mention in the InputBox


Hope it will clear now


Thanks


SP

 

[shrivallabha]

Maybe like this:


Shared workbook won't let you access VBA project [so you can't edit them while workbook is shared] but if you have macros in it then you can run them.

[pre]

Sub NewCopy2()
Dim strFileName As String

Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Dim wbDest As Workbook

Worksheets("Sheet1").Activate

Set rng1 = Range("A1:E7")
Set rng2 = Range("A80:E86")
Set myMultiRanges = Union(rng1, rng2)

strFileName = InputBox("Type a name for the new workbok", "File Name")
If Trim(strFileName) = vbNullString Then Exit Sub

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wbDest = Workbooks.Add

With wbDest
With .Sheets("Sheet1")
myMultiRanges.Copy .Range("A1")
.UsedRange.EntireColumn.AutoFit
End With
.SaveAs "C:UsersDesktop" & strFileName & ".xlsx", xlOpenXMLWorkbook
.Close
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

[/pre]

 

[sgmpatnaik]

@Shrivallabha


Hi


Great it's solve my problem


Thanks


SP