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.
- vba - speed up when moving old data to another sheet
- vba - i want to select a sheet and copy the contents to another sheet
- vba - only post unposted data from one excel book to another book
- i want to refer to another sheet in excel and display the corresponding value
- vba: cannot copy and paste to another sheet in the same workbook
- i want to copy a specific sheet to another file with vba
- vba - i want to use worksheetfunctionvlookup to get data from another sheet, but i can't refer to it
- vba - i want to post the data corresponding to another sheet
- how to refer the value of another sheet with vba form
- [vba] extract data of another sheet multiple times
- 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 excel data posting to another book
- vba - how to add a row to a sheet in another workbook
- vba - i want to generate another instance of excel and copy the sheet
- vba - how to change sheet name to cell value
- cannot open/close excel file with vba
- how to fix "result of excel merged cell width calculation by vba"
- with excel vba, the character in the cell is divided and displayed by line break
- vba - about excel macro
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- python - you may need to restart the kernel to use updated packages error
- python 3x - typeerror: 'method' object is not subscriptable
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - unresolved reference comes out in kotlin