• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Data from excel file to specific sheet

Hi,

Can anyone help me to solve my issue?
I have 3 excel files with the name as A, B, C and i want to copy data from sheets (Data) from each files and past it to master file which have sheet name A, B ,C.
In short, i want to copy data from sheet (Data) from File A and Past it into Sheet A in Master File.\

Thanks,
 

Marc L

Excel Ninja
Hi,​
without the necessary expected in an initial post as well explained in the forum rules​
- forums don't like guessing challenge except those for mind readers -​
you can start with the Macro Recorder operating manually : you will get your code base …​
For further help according to this code base, post it here - with the code tags - and well elaborate your need.​
 

jolivanes

Member
Code:
Sub From_Closed_Workbooks()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"    '<----Change as Required
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm")
            wb2.Sheets("Data").UsedRange.Copy ThisWorkbook.Sheets(wbks(i)).Cells(1, 1)
                ActiveWorkbook.Close False
    Next i
Application.ScreenUpdating = True
End Sub
 
Code:
Sub From_Closed_Workbooks()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"    '<----Change as Required
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm")
            wb2.Sheets("Data").UsedRange.Copy ThisWorkbook.Sheets(wbks(i)).Cells(1, 1)
                ActiveWorkbook.Close False
    Next i
Application.ScreenUpdating = True
End Sub
Hi Jolivanes,
Thanks for your script. It works perfectly as expected.
Anyway, there is one small issue regarding the alert. It shows like this. Can you help to advise what to do to skip this notification.
67638
Thanks,
Chanthan
 

jolivanes

Member
Are there formulas in that sheet?
Do you want to keep the links or not?
If you just copy the values.
Code:
Sub From_Closed_Files_2()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"    '<----Change as Required
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm")
            ThisWorkbook.Sheets(wbks(i)).Cells(1, 1).Resize(wb2.Sheets("Data").UsedRange.Rows.Count, _
            wb2.Sheets("Data").UsedRange.Columns.Count).Value = wb2.Sheets("Data").UsedRange.Value
                wb2.Close False
    Next i
Application.ScreenUpdating = True
End Sub
or try this keeping formulas
Code:
Sub From_Closed_Files()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
    Application.AskToUpdateLinks = False
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm", False)
        Application.AskToUpdateLinks = True
            wb2.Sheets("Data").UsedRange.Copy ThisWorkbook.Sheets(wbks(i)).Cells(1, 1)
                ActiveWorkbook.Close False
    Next i
Application.ScreenUpdating = True
End Sub
 
Last edited:
Are there formulas in that sheet?
Do you want to keep the links or not?
If you just copy the values.
Code:
Sub From_Closed_Files_2()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"    '<----Change as Required
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm")
            ThisWorkbook.Sheets(wbks(i)).Cells(1, 1).Resize(wb2.Sheets("Data").UsedRange.Rows.Count, _
            wb2.Sheets("Data").UsedRange.Columns.Count).Value = wb2.Sheets("Data").UsedRange.Value
                wb2.Close False
    Next i
Application.ScreenUpdating = True
End Sub
or try this keeping formulas
Code:
Sub From_Closed_Files()
Dim fldr As String, wbks, wb2 As Workbook, i As Long
fldr = "C:\Test\"
wbks = Array("A", "B", "C")
Application.ScreenUpdating = False
    For i = LBound(wbks) To UBound(wbks)
    Application.AskToUpdateLinks = False
        Set wb2 = Workbooks.Open(fldr & wbks(i) & ".xlsm", False)
        Application.AskToUpdateLinks = True
            wb2.Sheets("Data").UsedRange.Copy ThisWorkbook.Sheets(wbks(i)).Cells(1, 1)
                ActiveWorkbook.Close False
    Next i
Application.ScreenUpdating = True
End Sub
Hi,
Thanks for you help. Due to i prefer first option, i have used your provided code, but still the alert appear again.
Is there any error on that or is it because my problem of adjusting the code.
 

Marc L

Excel Ninja
As Workbooks.Open method has a parameter to not update links like you can check in VBA help …​
 
As Workbooks.Open method has a parameter to not update links like you can check in VBA help …​
Marc L
Thanks for comment. I found it now.

>>> use code - tags <<<
Code:
Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
        Set wb2 = Workbooks.Open....
Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
 
Last edited by a moderator:
Top