Clean up code

[Dorian C. Chalom]

Please let me know if there is a cleaner way to do this...

Sub CopyRangeToNewSheetAndNameValues()
With Sheets("Quote Form")
newname = .Range("h10")
.Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy
End With

Sheets.Add after:=Sheets(Sheets.Count)

With ActiveSheet
.Paste
.Range(.Range("a1"), .Range("a1").SpecialCells(xlLastCell)).Copy
.Range(.Range("a1"),
..Range("a1").SpecialCells(xlLastCell)).PasteSpecial Paste:=xlPasteValues
.Name = newname
.Range("a1").Select
End With

Worksheets("Quote Form").Activate
nCol = ActiveCell.SpecialCells(xlLastCell).Column
nRow = ActiveCell.SpecialCells(xlLastCell).Row

For iSht = 1 To Sheets.Count
If Sheets(iSht).Name = "Quote Form" Then
iSrcSht = iSht
End If
If Sheets(iSht).Name = Val(newname) Then
iDstSht = iSht
End If
Next iSht

For iCol = 1 To nCol
nSrcColWidth = Sheets(iSrcSht).Columns(iCol).ColumnWidth
Sheets(iDstSht).Columns(iCol).ColumnWidth = nSrcColWidth
Next iCol

For iRow = 1 To nRow
nSrcRowHeight = Sheets(iSrcSht).Rows(iRow).RowHeight
Sheets(iDstSht).Rows(iRow).RowHeight = nSrcRowHeight
Next iRow

Application.CutCopyMode = False

With Sheets("Quote Form")
.Range("B19:B46").ClearContents 'Item Number
.Range("H10:I11").ClearContents 'Invoice Number
.Range("G12:H12").ClearContents 'Address
End With
End Sub

 

[Tim Williams]

How about this ?

Sub CopyRangeToNewSheetAndNameValues()

Dim newname

With Sheets("Quote Form")
newname = .Range("h10").Value
.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
.Range("B19:B46").ClearContents 'Item Number
.Range("H10:I11").ClearContents 'Invoice Number
.Range("G12:H12").ClearContents 'Address
End With

With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
.Name = newname
.UsedRange.Value = .UsedRange.Value
End With

End Sub



Tim

 

[Dorian C. Chalom]

Tim;

This works really well...but why?
When I tried to Copy the sheet before it gave me errors because of the
lookup formulas attached to other workbooks. But in your code it works
great.

Also what does the UsedRange do?

Thank you

 

[Tim Williams]

I don't know why it wouldn't have worked before...

UsedRange is just that: the "used range" on the sheet, ie, it contains
(usually) all cells on a sheet which have been "used".

It's much quicker than doing something like this:

with ActiveSheet.cells
.value = .value
end with

Tim