Paste Method in Worksheet Class failed

[sleuth]

Fairly new to VBA, but I did search several other threads with this same error but none of those solutions seemed to work.

I'm trying to create a subroutine that prompts a user for a filename, then takes the data from that file and imports/copies it into the workbook which contains that subroutine.

I got it to work, but when I tried to add in some robustness to clear the sheet that it's importing first to make sure it is a clean copy, my paste action is now failing. I saw some solutions that suggested deleting the sheet and creating a replacement as a clean copy, but I don't want to do that for other reasons.

Here's my code. The ActiveSheet.Paste command is what is failing. Interestingly enough, if I removed the line from 2 lines above it (.UsedRange.Clear) the paste works, but I want to clear the page of both contents and formatting before the paste:

<code>
Sub FileBrowser()
Dim strPath As String
Dim wb As Workbook
strPath = Application.GetOpenFilename(, , "Select your File")
If strPath = "" Then Exit Sub
isOpen (GetFilenameFromPath(strPath))
Set wb = Application.Workbooks(GetFilenameFromPath(strPath))

' Copy data from survey results and paste in this file.
' Windows(GetFilenameFromPath(strPath)).Activate
wb.Activate

Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(1).Resize(, LastColumn).Select
Selection.Copy

ThisWorkbook.Activate
Sheets("Survey Results (Raw)").Select
Sheets("Survey Results (Raw)").UsedRange.Clear
Range("A1").Select
ActiveSheet.Paste



'Inserting a Column at Column E and name it Check
Range("E1").EntireColumn.Insert
Range("E1").Value = "Check"
Range("E1:E2").Merge

Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("E2:E" & LastRow).Value = "1"
End Sub
</code>

 

[sleuth]

I got it to work, but when I tried to add in some robustness to clear the sheet that it's pasting to first to make sure it is a clean copy, my paste action is now failing. I saw some solutions that suggested deleting the sheet and creating a replacement as a clean copy, but I don't want to do that for other reasons.

Here's my code. The ActiveSheet.Paste command is what is failing. Interestingly enough, if I removed the line from 2 lines above it (.UsedRange.Clear) the paste works, but I want to clear the page of both contents and formatting before the paste:

<code>
Sub FileBrowser()
Dim strPath As String
Dim wb As Workbook
strPath = Application.GetOpenFilename(, , "Select your File")
If strPath = "" Then Exit Sub
isOpen (GetFilenameFromPath(strPath))
Set wb = Application.Workbooks(GetFilenameFromPath(strPath))

' Copy data from survey results and paste in this file.
' Windows(GetFilenameFromPath(strPath)).Activate
wb.Activate

Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(1).Resize(, LastColumn).Select
Selection.Copy

ThisWorkbook.Activate
Sheets("Survey Results (Raw)").Select
Sheets("Survey Results (Raw)").UsedRange.Clear
Range("A1").Select
ActiveSheet.Paste



'Inserting a Column at Column E and name it Check
Range("E1").EntireColumn.Insert
Range("E1").Value = "Check"
Range("E1:E2").Merge

Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("E2:E" & LastRow).Value = "1"
End Sub
</code>

Editing for clarification.

 

[Fluff]

Try

Sub FileBrowser()
   Dim strPath As String
   Dim wb As Workbook
   Dim Ws As Worksheet
   
   Set Ws = ThisWorkbook.Sheets("Survey Results (Raw)")
   strPath = Application.GetOpenFilename(, , "Select your File")
   If strPath = "" Then Exit Sub
   IsOpen (GetFilenameFromPath(strPath))
   Set wb = Application.Workbooks(GetFilenameFromPath(strPath))
   
   ' Copy data from survey results and paste in this file.
   ' Windows(GetFilenameFromPath(strPath)).Activate
   Ws.Cells.Clear
   wb.Activate
   
   Dim LastColumn As Long
   LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
   Columns(1).Resize(, LastColumn).Select
   Selection.Copy Ws.Range("A1")
   
   'Inserting a Column at Column E and name it Check
   Ws.Range("E1").EntireColumn.Insert
   Ws.Range("E1").Value = "Check"
   Ws.Range("E1:E2").Merge
   
   Dim LastRow As Long
   LastRow = Ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Ws.Range("E2:E" & LastRow).Value = "1"
End Sub

 

[sleuth]

Fluff, that did the trick. I'm not sure why my version didn't.

However, I'm noticing every time I run it with your code, it adds another column at column E.

 

[sleuth]

Actually, it looks like it's adding the column in the source file (copied data) each time thru, and then pastes the source data plus the new columns every time i run it.

 

[sleuth]

Actually, it looks like it's adding the column in the source file (copied data) each time thru, and then pastes the source data plus the new columns every time i run it.

Solved it. I added a ThisWorkbook.Activate and a Sheets("Survey Results (Raw)").Select statements.

Here's the final solution. I'd still like to know why mine didn't work.

<code>
Sub FileBrowser()
Dim strPath As String
Dim wb As Workbook
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Survey Results (Raw)")
strPath = Application.GetOpenFilename(, , "Select your File")
If strPath = "" Then Exit Sub
isOpen (GetFilenameFromPath(strPath))
Set wb = Application.Workbooks(GetFilenameFromPath(strPath))

' Copy data from survey results and paste in this file.

ws.Cells.Clear
wb.Activate

Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(1).Resize(, LastColumn).Select
Selection.Copy ws.Range("A1")

'Inserting a Column at Column E and name it Check
ThisWorkbook.Activate
Sheets("Survey Results (Raw)").Select
Range("E1").EntireColumn.Insert
Range("E1").Value = "Check"
Range("E1:E2").Merge

Dim LastRow As Long
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("E2:E" & LastRow).Value = "1"
End Sub
</code>

 

[Fluff]

It's adding a new col E in Survey Results (Raw), as that's what your code looked to be doing.

 

[sleuth]

It does now, and it was originally. After I put in your suggested changes, it was not activating the Survey Results (Raw) so it was inserting column E into the source data. Then when I ran it again, it would copy the source data + the new column E and then create a column E again in the source data.

Now that I've added the ThisWorkbook.Activate, it seems to have fixed that.