Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA Copy Ranges From X Sheets & PasteSpecial

  1. #1
    Join Date
    31st March 2009
    Posts
    2

    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;

    VB:
    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 at 17:53.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th January 2007
    Posts
    570

    Re: Copy And Past Specialcode

    Hi shakeelm,
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    31st March 2009
    Posts
    2

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th January 2007
    Posts
    570

    Re: Copy And Past Specialcode

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,548

    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
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy different ranges from different sheets
    By salimthaj in forum EXCEL HELP
    Replies: 5
    Last Post: September 17th, 2005, 00:07
  2. PasteSpecial to Different Sheets, etc...
    By Bubbis Thedog in forum EXCEL HELP
    Replies: 4
    Last Post: February 11th, 2005, 02:25
  3. VBA: Copy - Pastespecial Format
    By mikebecker in forum EXCEL HELP
    Replies: 1
    Last Post: December 14th, 2004, 05:48
  4. Compare ranges on two sheets and copy value
    By rcoulter in forum EXCEL HELP
    Replies: 4
    Last Post: November 8th, 2004, 19:34
  5. VBA: Copy unique ranges to new sheets
    By xlite in forum EXCEL HELP
    Replies: 5
    Last Post: December 13th, 2003, 22:21

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno