Can anyone tell me how/where I would add code to this macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End(xlUp)(2)
i = i + 12
Loop
End Sub

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Dim rng1 as Range
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy
set rng1 = Worksheets("ClosingData") _
.Cells(Rows.Count, 1).End(xlUp)(2)
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
i = i + 12
Loop
End Sub

--
Regards,
Tom Ogilvy

"HJ" wrote in message
...
Can anyone tell me how/where I would add code to this macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End(xlUp)(2)
i = i + 12
Loop
End Sub

hi,
try this
rng.EntireRow.Copy
Worksheets("ClosingData").Cells(Rows.Count, 1).End(xlUp)(2)

-----Original Message-----
Can anyone tell me how/where I would add code to this
macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End
(xlUp)(2)
i = i + 12
Loop
End Sub
.

hi again,
oops Sorry.
I ment try this
change the copy command
rng.EntireRow.Copy
Worksheets("ClosingData").Cells(Rows.Count, 1).End
(xlUp).select
Selection.PasteSpecial xlpasteall

sheets("closings").select
Rng.select
untested but i have used this before.

-----Original Message-----
Can anyone tell me how/where I would add code to this
macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End
(xlUp)(2)
i = i + 12
Loop
End Sub
.

Tom,

Thanks so much!! Once again, you've saved me a lot of time!!

"Tom Ogilvy" wrote:

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Dim rng1 as Range
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy
set rng1 = Worksheets("ClosingData") _
.Cells(Rows.Count, 1).End(xlUp)(2)
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
i = i + 12
Loop
End Sub

--
Regards,
Tom Ogilvy

"HJ" wrote in message
...
Can anyone tell me how/where I would add code to this macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End(xlUp)(2)
i = i + 12
Loop
End Sub

Why?

xlPasteAll just takes two commands to do what the code is already doing in
one and doesn't do what the OP asked.

--
Regards,
Tom Ogilvy

wrote in message
...
hi again,
oops Sorry.
I ment try this
change the copy command
rng.EntireRow.Copy
Worksheets("ClosingData").Cells(Rows.Count, 1).End
(xlUp).select
Selection.PasteSpecial xlpasteall

sheets("closings").select
Rng.select
untested but i have used this before.

-----Original Message-----
Can anyone tell me how/where I would add code to this
macro to paste both
values and formats? TIA

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy Destination:= _
Worksheets("ClosingData").Cells(Rows.Count, 1).End
(xlUp)(2)
i = i + 12
Loop
End Sub
.

Tom..

I suggest reversing the pasting sequence has a few advantages
and may give fewer problems with text/dates and merged cells.

rng1.PasteSpecial xlFormats
rng1.PasteSpecial xlValues



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Tom Ogilvy" wrote :

Sub CopyClosingData()
Dim i As Long, rng As Range, sh As Worksheet
Dim rng1 as Range
Worksheets.Add(After:=Worksheets( _
Worksheets.Count)).Name = "ClosingData"
Set sh = Worksheets("Closings")
i = 12
Do While Not IsEmpty(sh.Cells(i, 1))
Set rng = Union(sh.Cells(i, 1), _
sh.Cells(i + 1, 1).Resize(2, 1))
rng.EntireRow.Copy
set rng1 = Worksheets("ClosingData") _
.Cells(Rows.Count, 1).End(xlUp)(2)
rng1.PasteSpecial xlValues
rng1.PasteSpecial xlFormats
i = i + 12
Loop
End Sub