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

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)
--
Gary''s Student - gsnu200811


"Jerry Foley" wrote:

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

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.

"Jerry Foley" wrote:

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

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

"Joel" wrote:

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.

"Jerry Foley" wrote:

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

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

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


"Jerry Foley" wrote:

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

"Joel" wrote:

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.

"Jerry Foley" wrote:

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