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
Set dataRng = .Range ("A8: g100") 'Range to copy
Dim c As Range
For Each c In dataRng.Cells
c.Formula = _
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
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.
- i want to copy a specific sheet to another file with vba
- vba - i want to post the data corresponding to another sheet
- vba excel data posting to another book
- vba - i want to select a sheet and copy the contents to another sheet
- vba - speed up when moving old data to another sheet
- how to refer the value of another sheet with vba form
- vba: cannot copy and paste to another sheet in the same workbook
- vba - how to add a row to a sheet in another workbook
- i want to copy a sheet to the end of another book with vba, but i get an error
- i want to copy and paste the entire excel file sheet of another book (choose freely each time)
- [vba] extract data of another sheet multiple times
- vba - only post unposted data from one excel book to another book
- vba - i want to generate another instance of excel and copy the sheet
- i want to copy only the value with the aggregate vba of the excel sheet
- i want to refer to another sheet in excel and display the corresponding value
- vba - i want to use worksheetfunctionvlookup to get data from another sheet, but i can't refer to it
- how to fix "result of excel merged cell width calculation by vba"
- ios - i want to launch datepicker specified in another file by clicking tableviewcell
- countermeasures for vba excel csv data changing arbitrarily
- about the character decoration in the user form of excel vba