Macro Functionality

J

Jerry Foley

Gien the following macro:
Option Explicit
Sub mastertest2()
Dim rSource As Range
Dim rCell As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

For Each wks In Worksheets(Array("SBX WORK SHEET"))
With wks
FirstRow = 4
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

For iRow = LastRow To FirstRow Step -1
Set rCell = wks.Cells(iRow, 4)
With rCell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
Worksheets("SBX COST SHEET").Rows(17).Insert
..EntireRow.Copy
Worksheets("SBX COST SHEET").Range("a17") _
..PasteSpecial Paste:=xlPasteValues
End If
End If
End With
Next iRow
Next wks
End Sub

Why does this macro copy the contecnts correctly but it copies them bolded,
centered and no text wrap to the destination?
Thanks
 
G

Gary''s Student

Because you are paste/special/values, the values in the destination cells
will be updated, but the formats will be the same as they were before the
paste.

(the paste will not change them)
 
J

Joel

You are pasting the values use PasteSpecial with the values option. This is
not copying the formating. Your code is inserting a new row 17. This new
row will have the same formating as Row 16.
 
J

Jerry Foley

Thanks,
What should be changed to reflect the same formatting and values as the main
sheet?
 
J

Joel

From
.EntireRow.Copy
Worksheets("SBX COST SHEET").Range("a17") _
.PasteSpecial Paste:=xlPasteValues

to
.EntireRow.Copy _
Destination:=Worksheets("SBX COST SHEET").Rows(17)
 

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