Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet to Desktop
Any body please help me.......
I would like to copy a range from an excel file (without any formula and with the format) and paste on desktop as a new excel file. For the above purpose I was using the below macro, but it is taking around 5 minutes to export this file to the desk top. Is there any other way to do so? Is there any error on the below macro? Kindly help on this matter. Sub MacroEmailPOB() Sheets("Email").Visible = True Sheets("Email").Select Cells.Select Selection.ClearContents Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Columns("A:A").Select Selection.ColumnWidth = 3.29 Columns("B:B").Select Selection.ColumnWidth = 4.86 Columns("C:C").Select Selection.ColumnWidth = 3.71 Columns("D:D").Select Selection.ColumnWidth = 19.14 Columns("E:E").Select Selection.ColumnWidth = 10.14 Columns("F:F").Select Selection.ColumnWidth = 9.43 Columns("G:G").Select Selection.ColumnWidth = 10 Columns("H:I").Select Selection.ColumnWidth = 8.14 Columns("J:J").Select Selection.ColumnWidth = 5.43 Sheets("CrewList").Select Range("total").Select Selection.Copy Sheets("Email").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("D12").Select ActiveWindow.SmallScroll Down:=-15 Range("A1").Select Sheets("CrewList").Select Application.CutCopyMode = False Range("A1").Select Sheets("Email").Select Range("A1").Select Sheets("Email").Select Sheets("Email").Copy ChDir "C:\Documents and Settings\radio\Desktop" ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\radio\Desktop\POB.xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close ActiveWindow.SelectedSheets.Visible = False Range("L7").Select Sheets("CrewList").Select Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Sheet to Desktop
I did not test this so it might hiccup. If so, post back and I'll fix it.
I basically just cleaned up the code by removing a lot of unneeded select and selection verbiage. Sub MacroEmailPOB() Sheets("Email").Visible = True With Sheets("Email") .ClearContents .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlNone .Borders(xlEdgeRight).LineStyle = xlNone .Borders(xlInsideVertical).LineStyle = xlNone .Borders(xlInsideHorizontal).LineStyle = xlNone End With With Sheets("Email") .Columns("A:A").ColumnWidth = 3.29 .Columns("B:B").ColumnWidth = 4.86 .Columns("C:C").ColumnWidth = 3.71 .Columns("D:D").ColumnWidth = 19.14 .Columns("E:E").ColumnWidth = 10.14 .Columns("F:F").ColumnWidth = 9.43 .Columns("G:G").ColumnWidth = 10 .Columns("H:I").ColumnWidth = 8.14 .Columns("J:J").ColumnWidth = 5.43 Sheets("CrewList").Range("total").Copy With Sheets("Email").Range("A1") .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With. Application.CutCopyMode = False ChDir "C:\Documents and Settings\radio\Desktop" ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\radio\Desktop\POB.xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close ActiveWindow.edSheets.Visible = False . Application.GoTo Sheets("CrewList").Range("A1"). End Sub " VLOOKUP fORMULA" wrote in message ... Any body please help me....... I would like to copy a range from an excel file (without any formula and with the format) and paste on desktop as a new excel file. For the above purpose I was using the below macro, but it is taking around 5 minutes to export this file to the desk top. Is there any other way to do so? Is there any error on the below macro? Kindly help on this matter. Sub MacroEmailPOB() Sheets("Email").Visible = True Sheets("Email").Select Cells.Select Selection.ClearContents Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Columns("A:A").Select Selection.ColumnWidth = 3.29 Columns("B:B").Select Selection.ColumnWidth = 4.86 Columns("C:C").Select Selection.ColumnWidth = 3.71 Columns("D:D").Select Selection.ColumnWidth = 19.14 Columns("E:E").Select Selection.ColumnWidth = 10.14 Columns("F:F").Select Selection.ColumnWidth = 9.43 Columns("G:G").Select Selection.ColumnWidth = 10 Columns("H:I").Select Selection.ColumnWidth = 8.14 Columns("J:J").Select Selection.ColumnWidth = 5.43 Sheets("CrewList").Select Range("total").Select Selection.Copy Sheets("Email").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("D12").Select ActiveWindow.SmallScroll Down:=-15 Range("A1").Select Sheets("CrewList").Select Application.CutCopyMode = False Range("A1").Select Sheets("Email").Select Range("A1").Select Sheets("Email").Select Sheets("Email").Copy ChDir "C:\Documents and Settings\radio\Desktop" ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\radio\Desktop\POB.xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close ActiveWindow.SelectedSheets.Visible = False Range("L7").Select Sheets("CrewList").Select Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Do I Copy Customized QAT from Desktop to Laptop? | New Users to Excel | |||
copy shortcut to desktop | Excel Programming | |||
Copy A File From Internet To My Desktop | Excel Programming | |||
Beforesave - Save copy on my desktop | Excel Programming | |||
It saves a copy on my desktop with a different file name? | Setting up and Configuration of Excel |