我正在嘗試將資料從關閉的作業簿復制到打開的作業簿中，設定如下：
- 關閉的作業簿（隨機檔案名，隨機作業表名稱）在單個作業表的 A 列中有資料。
- 打開作業簿需要將資料粘貼到下一個可用列中的現有“資料”表中
這看起來很簡單，但我一直在努力讓它作業，這是我在下面能做的最好的，但它回傳一個超出范圍的錯誤。
Sub Test
Dim fileName As Variant
Dim tableName, hideRow As String
Dim sheetRange As Range
Dim i As Integer
Dim freecolumn As Integer
Dim newWorkbook As Workbook
Dim currentbook As String
'open dialogue box to get new file to import
fileName = Application.GetOpenFilename
' run update in background
'Application.ScreenUpdating = False
ThisWorkbook.Activate
Sheets("Data").Select
freecolumn = ActiveSheet.UsedRange.Columns.Count 1
Set newWorkbook = Workbooks.Open(fileName, ReadOnly:=True)
' tried this method, but didnt work
'Workbooks(fileName).Worksheets(1).Range("A:A").Copy _
'Worksheets("Data").Range(freecolumn)
' also tried this
'Workbooks(fileName).Sheets(1).Columns(1).Copy Destination:=Workbooks(currentbook).Sheets("Data").Columns(freecolumn)
'Sheets("Data").Range(freecolumn).Resize(fileName.Rows.Count).Value = fileName.Value
'closedBook.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
'closedBook.Close SaveChanges:=False
'Application.ScreenUpdating = True
End Sub
為草率的代碼道歉，我從這個網站復制并粘貼了很多東西試圖讓它作業，現在我很困惑。
uj5u.com熱心網友回復：
這是你正在嘗試的嗎？（未經測驗）。我已經評論了代碼，但如果您發現任何錯誤或有任何疑問，請隨時在下面發表評論。
代碼
Option Explicit
Sub Sample()
Dim Ret As Variant
Dim wbThis As Workbook, wbThat As Workbook
Dim wsThis As Worksheet, wsThat As Worksheet
Dim LastCol As Long
'~~> Set your current workbook
Set wbThis = ThisWorkbook
'~~> This is the sheet where you want to copy the data to
Set wsThis = wbThis.Sheets("Data")
'~~> Finding last column in data sheet
With wsThis
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column 1
Else
LastCol = 1
End If
End With
'~~> Make user choose the file
Ret = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
'~~> If user presses cancel
If Ret = False Then Exit Sub
'~~> Open workbook
Set wbThat = Workbooks.Open(Ret)
'~~> Work with sheet 1
Set wsThat = wbThat.Sheets(1)
'~~> Copy and paste the columns
wsThat.Columns(1).Copy wsThis.Columns(LastCol)
'~~> Close the file without saving
wbThat.Close (False)
End Sub
值得一讀
- 避免使用 .Select
- 查找作業表中的最后一行/列
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345281.html