copy and paste by formats in tables

See table1 in sheet2
what I want is to copy the table1
and paste by formats to table2,table3,table4
see the result in sheet3
I want to do in the same sheet in sheet2 I don’t want the result in sheet3 I want it in sheet2
I want to do this by vba
SAMPLE119.xlsm
Avinash SinghAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

Sub CopyFormat()
Dim ws As Worksheet
Dim lr As Long, cnt As Long
Dim Rng As Range

Application.ScreenUpdating = False

Set ws = Sheets("Sheet2")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row

ws.Range("A1").CurrentRegion.Copy

For Each Rng In ws.Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 2).Areas
    cnt = cnt + 1
    If cnt > 1 Then
        Rng.Cells(1).PasteSpecial xlPasteFormats
    End If
Next Rng
Application.ScreenUpdating = True

End Sub

Open in new window

0
 
Avinash SinghAuthor Commented:
Thnx again neeraj sir for giving ur precious time to this post
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Avinash!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.