我的代碼有一點問題。我想將我的資料從一個檔案復制到我的主檔案。當我檢查代碼時，我遇到了一個問題。代碼在復制之前作業正常。在這一行
Set cell1 = wsDest.Cells(1, Range("B1").End(xlToRight).Column 1)
該單元格是從我正在獲取資料的檔案中選擇的，而不是我要粘貼到的檔案夾中。
我希望我的資料從這些其他檔案粘貼到主檔案中。我想將它們添加為列，而不是行。
Sub MoveCopyRowsColumns()
Dim mainWb As Workbook
Dim newWb As Workbook
Dim mainWs As Worksheet
Dim newWs As Worksheet
Dim strFolder As String
Set mainWb = Workbooks("Main_file.xlsm")
Set mainWs = mainWb.Worksheets("Worksheet1")
mainWs.Cells(1, Range("B1").End(xlToRight).Column 1).Select
mainWs.Columns(ActiveCell.Column).EntireColumn.Delete
strFolder = "C:\Users\User1\Desktop\Folder_with_files\"
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
Set newWb = Workbooks.Open(strFolder & strFile)
Set newWs = newWb.Sheets(1)
strFile = Dir
newWs.Cells(1, Range("B1").End(xlToRight).Column 1).Select
newWs.Columns(ActiveCell.Column).EntireColumn.Delete
newWs.Range("B1", Range("B1").End(xlDown).End(xlToRight)).Copy _
mainWs.Range("P1")
Loop
End Sub
uj5u.com熱心網友回復：
您必須宣告哪個檔案/作業表是哪個。每行都應參考正確的作業表。所有以單元格或范圍開頭的行都應首先具有作業表，例如：“mainWs.Cell”。在新檔案中，您沒有宣告任何作業表，只有作業簿 (wb)。我沒有嘗試過下面的代碼，但我希望它能解開你的問題思考。祝你好運！
Sub MoveCopyRowsColumns()
Dim mainWb As Workbook
Dim newWb As Workbook
Dim mainWs As Worksheet
Dim newWs As Worksheet
Dim strFolder As String
Dim strFile As String
Dim cell1 As Range
Set mainWb = Workbooks("Main_file.xlsm")
Set mainWs = mainWb.Worksheets("Worksheet1")
mainWs.Cells(1, Range("B1").End(xlToRight).Column 1).Select
mainWs.Columns(ActiveCell.Column).EntireColumn.Delete
'in my main file I delete the last column only one
strFolder = "C:\Users\User1\Desktop\Folder_with_files\"
strFile = Dir(strFolder & "*.xls*")
Do While strFile <> ""
Set newWb = Workbooks.Open(strFolder & strFile)
'Set the sheet you want to use, using "first sheet" or sheet by name
'Set newWs = newWb.Sheets(1)
'Set newWs = newWb.Worksheets("Worksheet1")
strFile = Dir
newWs.Cells(1, Range("B1").End(xlToRight).Column 1).Select
newWs.Columns(ActiveCell.Column).EntireColumn.Delete
'Set cell1 = newWs.Cells(1, Range("B1").End(xlToRight).Column 1)
newWs.Cells(1, Range("B1").End(xlToRight).Column 1).Copy
'the adress is taking from file when I take the data, not the main file which should take.
mainWs.Range(cell1).PasteSpecial Paste:=xlPasteValues
Loop
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/427716.html