#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    2
    Rep Power
    0

    Copying Multiple Ranges to another workbook not working


    Hi All!
    I created a VBA macro to copy data from specific worksheets in a workbook to another workbook with the same worksheet names. The code is looping through the worksheet correctly, but the code I have to copy specific ranges from the source workbook and then paste it to the target workbook is not working. The code will copy a single cell over in the loop, but not a range of cells. Below is my code.

    I thank you in advance for your assistance.

    - Ron -

    Code:
    Sub Copy_Data_Items()
         ' Copy items from Source Workbook to Target Workbook.
        
         
        Dim wbTarget As Workbook 'workbook where the data is to be pasted
        Dim wbSource As Workbook 'workbook from where the data is to copied
        Dim strSourceFile As String
        Dim strTargetFile As String
        Dim i As Long
         
        strSourceFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13old.xlsm"
        strTargetFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13new.xlsm"
         
        If MsgBox("Do You Want to Copy Data?", vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
        If MsgBox("Are you Sure?", vbYesNo + vbDefaultButton2) = vbNo Then Exit Sub
         
        Set wbSource = Workbooks.Open(strSourceFile)
        Set wbTarget = ActiveWorkbook
             
        For i = 1 To 12
            With wbTarget
                .Sheets(i + 1).Range("C12:G35") = wbSource.Sheets(i + 1).Range("C12:G35")
                .Sheets(i + 1).Range("C38:G52") = wbSource.Sheets(i + 1).Range("C38:G52")
                .Sheets(i + 1).Range("B60:G109") = wbSource.Sheets(i + 1).Range("B60:G109")
                .Sheets(i + 1).Range("B116:G215") = wbSource.Sheets(i + 1).Range("B116:G215")
                .Sheets(i + 1).Range("D216:G216") = wbSource.Sheets(i + 1).Range("D216:G216")
                .Sheets(i + 1).Range("I66:O85") = wbSource.Sheets(i + 1).Range("I66:O85")
                .Sheets(i + 1).Range("L086:O86") = wbSource.Sheets(i + 1).Range("L086:O86")
                .Sheets(i + 1).Range("I94:O108") = wbSource.Sheets(i + 1).Range("I94:O108")
                .Sheets(i + 1).Range("L109:O109") = wbSource.Sheets(i + 1).Range("L109:O109")
                .Sheets(i + 1).Range("I118:O126") = wbSource.Sheets(i + 1).Range("I118:O126")
                .Sheets(i + 1).Range("L127:O127") = wbSource.Sheets(i + 1).Range("L127:O127")
                .Sheets(i + 1).Range("I135:O144") = wbSource.Sheets(i + 1).Range("I135:O144")
                .Sheets(i + 1).Range("I153:O160") = wbSource.Sheets(i + 1).Range("I153:O160")
                .Sheets(i + 1).Range("L161:O161") = wbSource.Sheets(i + 1).Range("L161:O161")
                .Sheets(i + 1).Range("I169:O178") = wbSource.Sheets(i + 1).Range("I169:O178")
                .Sheets(i + 1).Range("I186:O215") = wbSource.Sheets(i + 1).Range("I186:O215")
                .Sheets(i + 1).Range("L216:O216") = wbSource.Sheets(i + 1).Range("L216:O216")
            End With
        
         Range("A1").Select
           
          Next i
         
        wbSource.Close True
    
    End Sub
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,453
    Rep Power
    4539
    Maybe someone who knows Excel programming will jump in. I don't, sorry.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    2
    Rep Power
    0

    Thanks Doug!


    Thank you Doug for taking the time to look at this. I know it is something simple that I am not seeing with the code.

    - Ron -


    Originally Posted by Doug G
    Maybe someone who knows Excel programming will jump in. I don't, sorry.

IMN logo majestic logo threadwatch logo seochat tools logo