Copy formulas and formatting between multiple sheets.

I would like to copy formulas to multiple workbooks. What is the best way to do this? Attached is code that I used to copy between two workbooks.  Sheet 1 would be where I would like to copy from, and sheet 2 would be whatever my active workbook is.


    Sheets("Sheet 1").Select
    Range("D64:P66").Select
    Selection.Copy
    
    Sheets("Sheet 2").Select
    Range("D64").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    Sheets("Sheet 1").Select
    Range("D64:P66").Select
    Application.CutCopyMode = False
        
        Selection.Copy
    Sheets("Sheet 2").Select
    Range("D64:P66").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Open in new window

wiredemc12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Just wanted to confirm is it multiple workbooks or worksheet? because in the code given i don't see you changing your workbook and the entire operations is been done in the same workbook...

Saurabh...
0
wiredemc12Author Commented:
It is just one workbook with multiple sheets.
0
Saurabh Singh TeotiaCommented:
Okay can you help me understand which range you want to copy to where? Also when you are copying from second worksheet where do you want to paste it??
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

wiredemc12Author Commented:
I want to copy the formulas :
   
 Sheets("Sheet 1").Select
    Range("D64:P66").Select
    Selection.Copy

Open in new window


To the exact range D64:P66 of any worksheet that I choose.
    Sheets("Sheet 2").Select
    Range("D64").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Open in new window

0
Saurabh Singh TeotiaCommented:
What about the next worksheet from where you want to copy the data?? and where you want to paste it??

Saurabh...
0
wiredemc12Author Commented:
I would like to be able to use ctrl shift j to run the macro on any sheet within the workbook. I want to paste it into the range:

 Range("D64:P66")
0
Saurabh Singh TeotiaCommented:
In that case you can simply run this code and it will move the formulas and formats rather everything from your activesheet where you are on to sheet2..

Sub movedata()

'
' Keyboard Shortcut: Ctrl+Shift+J

ActiveSheet.Range("D64:P66").Copy Sheets("Sheet2").Range("D64")
End Sub

Open in new window


Saurabh...
0
wiredemc12Author Commented:
No that is not what I am looking for. I wrote some psuedo code.

Sub rangePaste()

Dim ws As Worksheet
Dim myrange As Variant

myrange = Sheet5.Range("D64:P66")

For Each ws In Worksheets
    If ws.Name <> "Sheet 1" Then
        
    myrange.Copy ' this is where I need help to copy "myrange" range into each workbook.
        
    Range("D64").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    End If
    Next ws

End Sub

Open in new window

0
wiredemc12Author Commented:
    Dim ws As Worksheet
        
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet 1" Then
        
        Sheets("Sheet 1").Range("D64:P66").Copy
        
        Range("D64").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        

        End If
Next ws

    Application.ScreenUpdating = True
End Sub

Open in new window


This will work on one worksheet, but i cant get it to loop to the next worksheets
0
Saurabh Singh TeotiaCommented:
Their you go use this code..it will do what you are looking for..

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Sheet 1" Then

        Sheets("Sheet 1").Range("D64:P66").Copy

        ws.Range("D64").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


    End If
Next ws

Application.ScreenUpdating = True
End Sub

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.