Home>

vba - excel how to copy a specified sheet in another file

Copy a specific sheet from another Excel file to the currently open Excel sheet
I'm trying to build a program but it doesn't work.

If the target program specifies the file path, file name, and sheet name,
The range of a specific cell (A1: B10) is copied to the current specified sheet, eg sheet3.

The problem is that the original Excel sheet contains a macro,
The program seems to run when the seat is activated
An error will occur.

Therefore, I thought it would be good if I could copy the cell of the sheet directly without opening the file.
If the program directly refers to a specific cell on the sheet, the execution speed is abnormally slow.

Copying the original macro without moving it, or
If i know how to access other file sheets at high speed, please give me a professor.

By the way, the directly referenced program is as follows.

Dim wsPaste As Worksheet
Set wsPaste = ThisWorkbook.Sheets (3)
Dim dataRng As Range

'Spath = full file path
'Sfiles = file name

'Ssheet = sheet name

With wsPaste
Set dataRng = .Range ("A8: g100") 'Range to copy
End With

Dim c As Range

For Each c In dataRng.Cells

c.Formula = _
"= '"&Spath&_
"\ ["&Sfiles&"]"&Ssheet&"'!"&C.Address'

Next
With wsPaste.UsedRange
.Copy
.PasteSpecial xlPasteValues ​​
End With

vba
  • Answer # 1

    Is this the case?
    I don't know if it's fast.
    The operation has not been verified.

    Dim wsPaste As Worksheet
Set wsPaste = ThisWorkbook.Sheets (3)
Dim dataRng As Range
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
'Spath = full file path
'Sfiles = file name
'Ssheet = sheet name
Set wbSrc = Workbooks.Open (Spath&"\"&Sfiles)
Set wsSrc = wbSrc.Worksheets (Ssheet)
With wsPaste
    Set dataRng = .Range ("A8: g100") 'Range to copy
End With
Dim c As Range
For Each c In dataRng.Cells
    c.Value = wsSrc.Range (c.Address) .Value
Next
wbSrc.Close


    .

  • Answer # 2

      

    How to quickly access other file sheets

    In the first place, Excel is always working behind the scenes.
    If you want to refer to the sheet, write the following code so that the processing is about 8.5 times faster.

    'Write at the beginning of sheet update
Application.Calculation = xlCalculationManual 'Set calculation mode to manual
Application.EnableEvents = False 'Stop the event
Application.ScreenUpdating = False 'Stop updating screen display
'Write at end of sheet update
Application.Calculation = xlCalculationAutomatic 'Make calculation mode automatic
Application.EnableEvents = True 'Start event
Application.ScreenUpdating = True 'Start screen update update

    It doesn't matter, but if macros are embedded, you can delete all macros by changing the file format to .xlsx.

Related articles