# The selected range is invalid



## marc005 (May 21, 2013)

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 (May 21, 2013)

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 (May 21, 2013)

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 (May 21, 2013)

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


----------



## marc005 (May 21, 2013)

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


----------

