Announcement

Collapse
No announcement yet.

VBA Copy Ranges From X Sheets & PasteSpecial

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Copy Ranges From X Sheets & PasteSpecial

    Hi Everyone,
    I have a question similar to that ask before but slightly modified. My workbook contains 4 worksheets i.e. "General", "Inputs", "Monthly Profile", and "Annual Profile". Since i am a beginner to VBA, I need a VBA Code that will open a new workbook and copy the last three sheets as past special with the same format containing only values & not formulas. I already built a code but it only copy one sheet to the new workbook and not the other two at the same time. the code is;

    Code:
    Sub CopySheetValues()
    Dim ws As Worksheet, wb As Workbook
    Set ws = ActiveSheet
    Set wb = Workbooks.Add
    ws.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlValues
    ActiveSheet.Cells.PasteSpecial Paste:=xlFormats
    End Sub
    i want a VBA code that will copy the whole three sheets instead of one. Your prompt response will be appreciated. Thanks & Regards, Shakeel
    Last edited by Ger Plante; March 31st, 2009, 17:53.

  • #2
    Re: Copy And Past Specialcode

    Hi shakeelm,
    Code:
    Sub Macro4()
    
    Dim AddedWorkbookName As String
    'http://www.pcreview.co.uk/forums/thread-956817.php
    Dim NewSheets As Integer
    Dim MyArray
    
    MyArray = Array("General", "Inputs", "Monthly Profile", "Annual Profile")
    NewSheets = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 3
    Workbooks.Add
    Application.SheetsInNewWorkbook = NewSheets
    
    AddedWorkbookName = ActiveWorkbook.Name
    
    Application.ScreenUpdating = False
    
        For i = LBound(MyArray) + 1 To UBound(MyArray)
            ThisWorkbook.Sheets(MyArray(i)).Cells.Copy
            With Workbooks(AddedWorkbookName).Sheets(i).Cells
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
            End With
        Next
        
    Application.ScreenUpdating = True
    
    End Sub
    Regards,
    Junho

    Comment


    • #3
      Re: Copy And Past Specialcode

      Thanks Junho,

      While running the code i found a compile error message explaining that the variable "i" is not defined. can u help me fix that. i would be much thankfull.

      Regards,
      Shakeel

      Comment


      • #4
        Re: Copy And Past Specialcode

        Hi shakeelm,
        Add
        Code:
        Dim i As Integer
        Regards,
        Junho

        Comment


        • #5
          Re: Copy And Past Specialcode

          Hi Shakeel,

          Welcome to the forum.

          I have fixed a spelling mistake in your thread title.

          Please used CODE tags when putting any code into a post. I have added them for you this time.

          Since this is the Hire Help forum, you need to indicate how much you are willing to pay to get a question answered. I have locked your post for the moment. Please PM either myself, or Dave Hawley to indicate how much you are willing to pay and we will unlock the thread.

          Thanks
          Ger

          Check out our new reputation system. Click on the "star" under the post!
          _______________________________________________

          There are 10 types of people in the world. Those that understand Binary and those that dont.

          Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

          The BEST Lookup function of all time

          Dynamic Named Ranges are your bestest friend

          _______________________________________________

          Comment

          Working...
          X