bigpappi23
New Member
- Joined
- Feb 1, 2013
- Messages
- 38
Hi guy,
i have this code for copying diffrent ranges of cells and saving it in different workbooks:
Sub NewCopy()
Dim strFileName As String
Dim range1 As Range
Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("ACL & History").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:\Users\zztongl\" & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
the problem is when is share the workbook, it generated the runtime error '1004' Command is not available in a shared workbook...can anyone help me to make it work even in a shared workbook.
thanks in advance guys,
this forum really helped me with my vba questions
i have this code for copying diffrent ranges of cells and saving it in different workbooks:
Sub NewCopy()
Dim strFileName As String
Dim range1 As Range
Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("ACL & History").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:\Users\zztongl\" & strFileName & ".xlsm", xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
the problem is when is share the workbook, it generated the runtime error '1004' Command is not available in a shared workbook...can anyone help me to make it work even in a shared workbook.
thanks in advance guys,
this forum really helped me with my vba questions