The selected range is invalid

[marc005]

Why do I get:

"The selected range is invalid. Select a valid range. A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table."

with this excel data file

https://dl.dropboxusercontent.com/s...UK44Kia9Ao_YRlO5zgIHX2qpFVME_GsX3DZPZKQg&dl=1

when I click on PowerPivot --> Create Linked Table . I dont understand what is wrong

 

[MD610]

It doesn't like that you are trying to import queries from external data connections to regular excel into PowerPivot.

Can you connect PowerPivot directly to the CSV's without going thru an excel worksheet.

If you need all the CSV's combined into a file that can be imported to PowerPivot as one table, you can try something like this:
Combine Multiple Worksheets/Workbooks into a Single PowerPivot Table « PowerPivotPro

 

[marc005]

thanx mike! I dont understand how excel can detect that. I mean I just copy and pasted the data from a workbook
where the data was indeed merged from many workbooks into a clean workbook. Is there anyway that I can trick
excel into believing I typed in the data?

Sub LoadFolder()
Dim folderPath As String
Dim tempbook As Workbook

Sheets("Sheet2").Select
Cells.Select
Selection.Delete Shift:=xlUp

folderPath = "C:\Users\marc\Dropbox\Apps\Attachments" 'Your folder goes here

Set fso = CreateObject("Scripting.FileSystemObject")
Set mFolder = fso.GetFolder(folderPath)

For Each mFile In mFolder.Files

Lastrow = Range("A" & Rows.Count).End(xlUp).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & mFile.Path, _
Destination:=Range("$A$" & Lastrow + 1))
.Name = "endofdayequities20130510"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Range("A" & Lastrow + 1 & ":A" & Lastrow + 1).EntireRow.Delete


Next mFile

Range("A1:A4").EntireRow.Delete
Range("B:B,E:AQ").Select
Selection.Delete Shift:=xlToLeft

Range("A1").Select

End Sub

 

[marc005]

Sweet I did it. Copy and past only values! Than PowerPiwot worked.
Why did I not think about that before

 

[marc005]

If you do like this it works

Public Sub k()
Worksheets("Sheet1").Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Sheet3").Range("A1:C" & finalrow).Value = Range("A1:C" & finalrow).Value

End Sub

However I must say that it is silly and stupid. Why do need to jump through one extra hoops?! silly microsoft....