Copy and paste VBA

[jose001]

Hi guys,

I have this little piece of code which some of you kindly helped me with a few weeks back. As you can see it copies a range from several sheets and pastes it into another called Printout. However, some of the cells contain formulas which are messing up. Is it possible to change the code to reflect a paste special (values) rather than a straight copy? Here is the code...

***************************************************************
Private Sub PrintHC_Click()
Dim i As Long
i = 2

Worksheets("Printout").Select

For Each sh In Worksheets
If sh.Name <> "Begin" And sh.Name <> "Index" And sh.Name <> "Codes" And sh.Name <> "Printout" Then
sh.Range("A2:M12").Copy Worksheets("Printout").Range("A" & i)
i = Worksheets("Printout").Cells(Rows.Count, 1).End(xlUp).Row + 2
End If
Next sh



End Sub

*********************************************************

Many Thanks

Jose

 

[Mike Blackman]

Try:

Private Sub PrintHC_Click()
Dim i As Long

i = 2

Worksheets("Printout").Select

For Each sh In Worksheets
    If sh.Name <> "Begin" And sh.Name <> "Index" And sh.Name <> "Codes" And sh.Name <> "Printout" Then
        sh.Range("A2:M12").Copy
             Worksheets("Printout").Range("A" & i).[B][COLOR="Red"]PasteSpecial Paste:=xlValues[/COLOR][/B]
        i = Worksheets("Printout").Cells(Rows.count, 1).End(xlUp).Row + 2
    End If
Next sh

End Sub

 

[Simon Lloyd]

Keeping the format you had and not tested

Private Sub PrintHC_Click()
Dim i As Long
i = 2

Worksheets("Printout").Select

For Each sh In Worksheets
If sh.Name <> "Begin" And sh.Name <> "Index" And sh.Name <> "Codes" And sh.Name <> "Printout" Then
sh.Range("A2:M12").Copy

With Worksheets("Printout").Range("A" & i)
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End With
i = Worksheets("Printout").Cells(Rows.Count, 1).End(xlUp).Row + 2
End If
Next sh


End Sub

 

[jose001]

Hey Mikey,

thanks for the quick response, that works great but.....I just realised that I have a shaded background with borders and now it's obviously just copying the values How on earth can I get around copying the whole table and just values?

 

[njimack]

Private Sub PrintHC_Click()
Dim i As Long
i = 2

Worksheets("Printout").Select

For Each sh In Worksheets
If sh.Name <> "Begin" And sh.Name <> "Index" And sh.Name <> "Codes" And sh.Name <> "Printout" Then
sh.Range("A2:M12").Copy 

With Worksheets("Printout").Range("A" & i)
.PasteSpecial xlvalues
.PasteSpecial xlFormats
End With

Application.CutCopyMode = False 'clears the clipboard

i = Worksheets("Printout").Cells(Rows.Count, 1).End(xlUp).Row + 2
End If
Next sh

End Sub

 

[jose001]

Thanks so much both, that worked a treat.

Many many thanks for the help as always!

Jose