Clean up code

  • Thread starter Dorian C. Chalom
  • Start date
D

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
 
T

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
 
D

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top