none
How do you transfer a range from one workbook to an open workbook with a macro? RRS feed

  • Question

  • I am new to the EXCEL VBA and I am trying to transfer some data from 3 different sheets in a closed workbook to the one that I am opening and place them in 3 different sheets, by using a macro. I have a code that does a part of this with one sheet at a time, but it gives me a  running-time error 1004. I would very much appreciate if someone can help me with this issue. 

    Sub DataTransfer()

        Workbooks.Open Filename:="C:\INfashion\INfashionSalesExpences.xlsm"
        Sheets("Expences").Activate
        Range("ExpencesData").Select
        Selection.Copy
        ActiveWorkbook.Save
        ActiveWorkbook.Close

        Unprotect_All
        Sheet7.Activate
        Range("A3").PasteSpecial Paste:=xlPasteAll, operation:=xlNone

    End Sub

            
    Saturday, October 1, 2016 12:24 PM

Answers

  • May be

    Sub Grab_Data_From_Closed_Workbook()
        Dim fPath       As String
        Dim wbData      As Workbook
        Dim wsMaster    As Worksheet
        Dim Ws          As Worksheet
    
        'Set The Sheet Where To Import Your Data
        Set wsMaster = ThisWorkbook.Sheets("Sheet1")
    
        'Change Your Path
        fPath = "C:\INfashion\INfashionSalesExpences.xlsm"
    
        Application.ScreenUpdating = False
            Set wbData = Workbooks.Open(fPath)
            
            For Each Ws In wbData.Worksheets
                Ws.UsedRange.Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next Ws
        
            wbData.Close False
        Application.ScreenUpdating = True
        
        MsgBox "Done...", 64
    End Sub


    Saturday, October 1, 2016 6:06 PM
  • Another approach as I noticed you need to transfer each sheet to separated sheet

    Sub Grab_Data_From_Closed_Workbook()
        Dim fPath       As String
        Dim wbData      As Workbook
        Dim Ws          As Worksheet
        Dim I           As Integer
        
        'Change Your Path
        fPath = "C:\Users\Future\Desktop\Sample.xlsm"
    
        Application.ScreenUpdating = False
            Set wbData = Workbooks.Open(fPath)
            
            For Each Ws In wbData.Worksheets
                I = I + 1
                Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ActiveSheet.Name = "Output" & I
            Next Ws
        
            wbData.Close False
        Application.ScreenUpdating = True
        
        MsgBox "Done...", 64
    End Sub

    Saturday, October 1, 2016 6:19 PM

All replies

  • May be

    Sub Grab_Data_From_Closed_Workbook()
        Dim fPath       As String
        Dim wbData      As Workbook
        Dim wsMaster    As Worksheet
        Dim Ws          As Worksheet
    
        'Set The Sheet Where To Import Your Data
        Set wsMaster = ThisWorkbook.Sheets("Sheet1")
    
        'Change Your Path
        fPath = "C:\INfashion\INfashionSalesExpences.xlsm"
    
        Application.ScreenUpdating = False
            Set wbData = Workbooks.Open(fPath)
            
            For Each Ws In wbData.Worksheets
                Ws.UsedRange.Copy wsMaster.Range("A" & Rows.Count).End(xlUp).Offset(1)
            Next Ws
        
            wbData.Close False
        Application.ScreenUpdating = True
        
        MsgBox "Done...", 64
    End Sub


    Saturday, October 1, 2016 6:06 PM
  • Another approach as I noticed you need to transfer each sheet to separated sheet

    Sub Grab_Data_From_Closed_Workbook()
        Dim fPath       As String
        Dim wbData      As Workbook
        Dim Ws          As Worksheet
        Dim I           As Integer
        
        'Change Your Path
        fPath = "C:\Users\Future\Desktop\Sample.xlsm"
    
        Application.ScreenUpdating = False
            Set wbData = Workbooks.Open(fPath)
            
            For Each Ws In wbData.Worksheets
                I = I + 1
                Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                ActiveSheet.Name = "Output" & I
            Next Ws
        
            wbData.Close False
        Application.ScreenUpdating = True
        
        MsgBox "Done...", 64
    End Sub

    Saturday, October 1, 2016 6:19 PM