Import specific range from another workbook

[JarredSpec]

Hey guys, stuck with this one.

Found this code online:

<code class="vb keyword">Sub</code> <code class="vb plain">ImportDatafromotherworksheet()</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">wkbCrntWorkBook </code><code class="vb keyword">As</code> <code class="vb plain">Workbook</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">wkbSourceBook </code><code class="vb keyword">As</code> <code class="vb plain">Workbook</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">rngSourceRange </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb spaces">    </code><code class="vb keyword">Dim</code> <code class="vb plain">rngDestination </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>
<code class="vb spaces">    </code><code class="vb keyword">Set</code> <code class="vb plain">wkbCrntWorkBook = ActiveWorkbook</code>
<code class="vb spaces">    </code><code class="vb keyword">With</code> <code class="vb plain">Application.FileDialog(msoFileDialogOpen)</code>
<code class="vb spaces">        </code><code class="vb plain">.Filters.Clear</code>
<code class="vb spaces">        </code><code class="vb plain">.Filters.Add </code><code class="vb string">"Excel 2007-13"</code><code class="vb plain">, </code><code class="vb string">"*.xlsx; *.xlsm; *.xlsa"</code>
<code class="vb spaces">        </code><code class="vb plain">.AllowMultiSelect = </code><code class="vb keyword">False</code>
<code class="vb spaces">        </code><code class="vb plain">.Show</code>
<code class="vb spaces">        </code><code class="vb keyword">If</code> <code class="vb plain">.SelectedItems.Count > 0 </code><code class="vb keyword">Then</code>
<code class="vb spaces">            </code><code class="vb plain">Workbooks.Open .SelectedItems(1)</code>
<code class="vb spaces">            </code><code class="vb keyword">Set</code> <code class="vb plain">wkbSourceBook = ActiveWorkbook</code>
<code class="vb spaces">            </code><code class="vb keyword">Set</code> <code class="vb plain">rngSourceRange = Application.InputBox(prompt:=</code><code class="vb string">"Select source range"</code><code class="vb plain">, Title:=</code><code class="vb string">"Source Range"</code><code class="vb plain">, </code><code class="vb keyword">Default</code><code class="vb plain">:=</code><code class="vb string">"A1"</code><code class="vb plain">, Type:=8)</code>
<code class="vb spaces">            </code><code class="vb plain">wkbCrntWorkBook.Activate</code>
<code class="vb spaces">            </code><code class="vb keyword">Set</code> <code class="vb plain">rngDestination = Application.InputBox(prompt:=</code><code class="vb string">"Select destination cell"</code><code class="vb plain">, Title:=</code><code class="vb string">"Select Destination"</code><code class="vb plain">, </code><code class="vb keyword">Default</code><code class="vb plain">:=</code><code class="vb string">"A1"</code><code class="vb plain">, Type:=8)</code>
<code class="vb spaces">            </code><code class="vb plain">rngSourceRange.Copy rngDestination</code>
<code class="vb spaces">            </code><code class="vb plain">rngDestination.CurrentRegion.EntireColumn.AutoFit</code>
<code class="vb spaces">            </code><code class="vb plain">wkbSourceBook.Close </code><code class="vb keyword">False</code>
<code class="vb spaces">        </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces">    </code><code class="vb keyword">End</code> <code class="vb keyword">With</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

But rather than it opening input boxes to select the range, I want it to automatically default to D103:Y200 in the workbook you've gone looking for (It will always be these cells), and drop it directly into Sheet 2 starting at Cell A1 and have modified it like so:

(Spoiler alert, it's not working)

Sub ImportDatafromotherworksheet()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set SourceRange = Range("D103:Y200")
            wkbCrntWorkBook.Activate
            Set rngDestination = Sheet2.Range("A1")
            rngSourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close True
        End If
    End With
End Sub

Now the debugger has a problem with the rngSourceRange.Copy rngDestination entry. I'm fresh out of ideas to get it working.

Any help appreciated

 

[MARK858]

Your source range is called SourceRange but then you are using rngSourceRange which is giving you the error (obviously you could make both rngSourceRange rather than SourceRange).

You should also explicitly include the sheet name (and I would use the workbook variable too), change the sheet name to suit...

Sub ImportDatafromotherworksheet()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngDestination As Range, SourceRange As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set SourceRange = wkbSourceBook.Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]").Range("D103:Y200")
            wkbCrntWorkBook.Activate
            Set rngDestination = Sheet2.Range("A1")
            SourceRange.Copy rngDestination
            rngDestination.CurrentRegion.EntireColumn.AutoFit
            wkbSourceBook.Close True
        End If
    End With
End

Sub

 

[JarredSpec]

Cheers! After a little tweak and I got it working.

Thanks heaps

 

[MARK858]

Only tweak other than the sheet name should have been I managed to get the word "Sub" outside the code tags somehow Happy it helped