|
我的目的是将不同工作表的内容复制粘贴到指定工作表的指定区域内。
奇怪的是,我运行完这串代码,只有book4的sheet2 被复制粘贴到了Autoload1 的sheet3上,另一个没成功。
自己也实在看不出来问题在哪儿,能否请大神们帮我看下,谢谢。
sourcing | | Destination | DIR | file name | sheet name | range | | file name | sheet name | range | C:\Users\19008483\Desktop | Book4.xls | Sheet1 | a1:q500 | | Autoload1.xlsm | sheet2 | a1 | C:\Users\19008483\Desktop | Book4.xls | Sheet2 | a1:q501 | | Autoload1.xlsm | sheet3 | a1 | | | | | | | | |
Sub test()
Dim name As String, i%, file, sht, rng, destination, desfile, dessht As String
Application.ScreenUpdating = False
On Error Resume Next
For i = 6 To 10
name = Range("b" & i).Text & "\" & Range("c" & i).Text
file = Range("c" & i).Text
sht = Range("d" & i).Text
rng = Range("e" & i).Text
destination = Range("i" & i).Text
desfile = Range("g" & i).Text
dessht = Range("h" & i).Text
If file = "" Then GoTo 100
Workbooks.OpenText Filename:=name
Windows(file).Activate
Sheets(sht).Range(rng).Select
Selection.Copy
Windows(desfile).Activate
Sheets(dessht).Select
Range(destination).Select
ActiveSheet.Paste
Workbooks.OpenText Filename:=name
ActiveWindow.Close
Windows("Autoload1.xlsm").Activate
Sheets("Sheet1").Select
Next
100
Application.ScreenUpdating = True
End Sub
- Sub test()
- Dim name As String, i%, file, sht, rng, destination, desfile, dessht As String
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- 'On Error Resume Next
- For i = 6 To 10
- name = Range("b" & i).Text & "" & Range("c" & i).Text
- file = Range("c" & i).Text
- sht = Range("d" & i).Text
- rng = Range("e" & i).Text
- destination = Range("i" & i).Text
- desfile = Range("g" & i).Text
- dessht = Range("h" & i).Text
-
- If file = "" Then GoTo 100
- Workbooks.OpenText Filename:=name
-
- Windows(file).Activate
- Sheets(sht).Select
- Range(rng).Select
- Selection.Copy
- Windows(desfile).Activate
-
- Sheets(dessht).Select
- Range(destination).Select
- Selection.PasteSpecial Paste:=xlPasteValues
-
- 'Workbooks.OpenText Filename:=name
-
- Windows(file).Close
-
- Windows("Autoload1.xlsm").Activate
- Sheets("Sheet1").Select
-
- Next
- 100
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- End Sub
复制代码
|
|