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>
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>