VBA Destination range is not large enough..

[afc171]

Hi all,

I am coping a selection of data to another sheet but keep getting message that the destination range is not large enough to accommodate all copied rows ?

I click Yes and it copies over fine but is there a way to ignore the message or automatically click Yes?

    Dim rgData As range, rgCriteria As range, rgOuput As range
    
    Set rgData = ThisWorkbook.Worksheets("LCStock").range("A5").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("LCStock").range("F3").CurrentRegion
    Set rgOuput = ThisWorkbook.Worksheets("Required_Items").range("A2").CurrentRegion

    rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOuput

Thank you

 

[MARK858]

Untested so not sure exactly what you'll get

    Dim rgData As Range, rgCriteria As Range, rgOuput As Range
   
    Set rgData = ThisWorkbook.Worksheets("LCStock").Range("A5").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("LCStock").Range("F3").CurrentRegion
    Set rgOuput = ThisWorkbook.Worksheets("Required_Items").Range("A2")
   
    rgData.AdvancedFilter xlFilterInPlace, rgCriteria
   
    With rgData.SpecialCells(12)
        rgOutput.Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[afc171]

Filters but nothing is copied over mate

 

[MARK858]

Again untested

    Dim rgData As Range, rgCriteria As Range, rgOutput As Range
 
    Set rgData = ThisWorkbook.Worksheets("LCStock").Range("A5").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("LCStock").Range("F3").CurrentRegion
    Set rgOutput = ThisWorkbook.Worksheets("Required_Items").Range("A2")
 
    rgData.AdvancedFilter xlFilterInPlace, rgCriteria
 
    With rgData
        rgOutput.Resize(.Rows.Count, .Columns.Count).Value = .SpecialCells(12).Value
    End With

or

    Dim rgData As Range, rgCriteria As Range, rgOutput As Range
 
    Set rgData = ThisWorkbook.Worksheets("LCStock").Range("A5").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("LCStock").Range("F3").CurrentRegion
    Set rgOutput = ThisWorkbook.Worksheets("Required_Items").Range("A2")
 
    rgData.AdvancedFilter xlFilterInPlace, rgCriteria
 
        rgData.SpecialCells(12).Copy
        rgOutput.PasteSpecial xlValues

 

[afc171]

Compile error:
Variable not defined

Highlights the sub name when you run it on both codes

 

[Marc L]

Hi,​

​

link your workbook with a files host website and well elaborate what your initial code is trying to achieve​

in order we can post a clean code without the useless but just with the necessary for efficiency …​

 

[MARK858]

That's because I didn't amend your rgOuput to rgOutput when I copied it. Try the amended codes in the post

 

[afc171]

That's because I didn't amend your rgOuput to rgOutput when I copied it. Try the amended codes in the post

Thanks mate, this one has worked as required!

Dim rgData As Range, rgCriteria As Range, rgOutput As Range

    Set rgData = ThisWorkbook.Worksheets("LCStock").Range("A5").CurrentRegion
    Set rgCriteria = ThisWorkbook.Worksheets("LCStock").Range("F3").CurrentRegion
    Set rgOutput = ThisWorkbook.Worksheets("Required_Items").Range("A2")

    rgData.AdvancedFilter xlFilterInPlace, rgCriteria

        rgData.SpecialCells(12).Copy
        rgOutput.PasteSpecial xlValues

Thanks alot for your help

Also noticed the actual first problem doesn't show up now either!

 

[MARK858]

You're welcome (remember to put the line Application.CutCopyMode = False after those lines if using that code).