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]

My answer was in response to post 8 (my fault should have specified it).
What was the exact code you used when you got the below?

ah yes good spot, should be output!

ok added clearformats but have run-time error 1004

Method 'AdvancedFilter" of object 'Range' failed

 

[afc171]

My answer was in response to post 8 (my fault should have specified it).
What was the exact code you used when you got the below?

it was this line..

    rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput.ClearFormats

 

[MARK858]

It should be a separate line

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
rgOutput.ClearFormats

 

[afc171]

It should be a separate line

rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
rgOutput.ClearFormats

Doesn't seem to work, for some reason still overwrites the formatting on the other sheet.

 

[MARK858]

Ok, we'll start from scratch. What do you get with

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

    With rgData
        rgOutput.Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

 

[afc171]

ok, that seems to copy everything from LCStock and duplicate it into Required_Items, so does not copy only the filtered items.

Although, it does not overwrite the formatting in Required_Items!

 

[MARK858]

Does the formatting change?

 

[afc171]

Does the formatting change?

It is keeping the formatting in Required_Items sheet once copied.

 

[MARK858]

Then I think that your best option is to do a filter in place and copy that

    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 = .Value
    End With

 

[afc171]

That seems to filter the data, but also copying everything over for some reason