vba error when copy/paste without clipboard

DDT~123

Board Regular
Joined
May 31, 2012
Messages
220
Hello all - I'm receiving a "missing object" error in the below code. This is only a smidgen of much a much larger code which copies 100 text files into 100 worksheets. The range in File2 may vary, but will never be larger than A1:R55. Any assistance in having this copy/paste without clipboard would be greatly appreciated.


Code:
Public Sub ImportCMS()
Dim File1, File2 As String
File1 = ActiveWorkbook.Name
ws = ActiveSheet.Name

With Sheets("account1")
.Range(.Cells(10, 1), .Cells(64, 18)).ClearContents
End With
File2 = "C:\Users\dturner\Desktop\ServiceLevelsSMS\IntervalScriptOutput\account1.txt"
Workbooks.Open File2
wFile = ActiveWorkbook.Name
With Range("A1:R55")
    Windows(File1).Sheets(ws).Range("A10").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Application.CutCopyMode = False
Workbooks(wFile).Close False
If ActiveSheet.Name = "Sheet1" Then
Application.GoTo Reference:="mTop"
End If

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Where in the code do you get the error?
 
Upvote 0
Which workbook are you trying to refer to there?
 
Upvote 0
Upvote 0
I appreciate your help, but I tried the code you provided which didn't work. It kept pasting File2 values in all the tabs instead of File3, File4, etc.
Just because it is copying to and from the wrong tabs doesn't mean you need to bin the whole idea, surely it just means correcting the source and destination for the copy and paste. As you cans see from Norie comment your requirement are not exactly crystal clear.
Also in the Forum Guidelines it quite clearly states:
  1. If the answer you receive is inadequate for some reason, post a reply stating why the answer does not work and/or with more information (if needed) in the same thread.
  2. If your question has not received any replies, do not start a new thread.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top