Attempting to set column value=column value on another sheet with only visible cells VBA

[sax2play]

Hello Experts!

I am attempting to move data from one sheet to another without select/copy/paste due to the amount of data. I first filter the worksheet and then set range.value of new sheet = range.value of original sheet; however, I cannot get it to move only the visible cells. Thoughts??

last_row2 = shEB.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
    last_col2 = shEB.AutoFilter.Range.Rows(1).SpecialCells(xlCellTypeVisible).Cells.Count
    
    'Reformat/Move columns to new Easel Board Review Sheet
    Do While WB.Sheets("Config_Review").Cells(includedCol, 1) <> ""
    
        If WB.Sheets("Config_Review").Cells(includedCol, 1) = "" Then
            Set rngFound = WB.Sheets("EaselBoard_1").Range("1:1").Find(What:=WB.Sheets("Config_Review").Cells(includedCol, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        Else
            colString = WB.Sheets("Config_Review").Cells(includedCol, 1)
            Set rngFound = shEB.Range("1:1").Find(What:=colString, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
        End If
                
        If Not rngFound Is Nothing Then
            shNewEB.Range(shNewEB.Cells(1, 1), shNewEB.Cells(last_row2, last_col2)).SpecialCells (xlCellTypeVisible)
            shNewEB.Range(shNewEB.Cells(1, Col), shNewEB.Cells(last_row2, Col)).Value = shEB.Range(shEB.Cells(1, rngFound.Column), shEB.Cells(last_row2, rngFound.Column)).Value
            
        Else
            If colString = "" Then
                MsgBox "Cannot find column: " & WB.Sheets("Config_Review").Cells(includedCol, 1)
                End
            Else
                MsgBox "Cannot find column matching: " & colString
                
            End If
            
            Col = Col - 1
                       
        End If
        
        Col = Col + 1
        includedCol = includedCol + 1
        
    Loop

Thanks!

 

[StephenCrump]

I am attempting to move data from one sheet to another without select/copy/paste due to the amount of data. I first filter the worksheet and then set range.value of new sheet = range.value of original sheet; however, I cannot get it to move only the visible cells. Thoughts??

You say move twice, but I assume you mean copy?

And it's not clear why you want to adopt this approach. Won't it be less efficient than a simple Filter and Copy/Paste?

 

[sax2play]

You say move twice, but I assume you mean copy?

And it's not clear why you want to adopt this approach. Won't it be less efficient than a simple Filter and Copy/Paste?

In my original macro, I copy and pasted the data (30k+ rows, 100+ columns) and it took 45 minutes. I changed it to set column new.value = column original.value and it dropped the time down to under 4 minutes, which was why I wanted to move the data. However, now that I changed the macro again to be dynamic and using loops, I was able to use range.copy and set destination and it is working well with no additional time.

 

[sax2play]

I changed the problem line above to:

If Not rngFound Is Nothing Then
            shEB.Range(shEB.Cells(1, rngFound.Column), shEB.Cells(last_row, rngFound.Column)).SpecialCells(xlCellTypeVisible).Copy _
                Destination:=shNewEB.Range(shNewEB.Cells(1, Col), shNewEB.Cells(last_row, Col))

Which resolved the error.

Thanks for the help StephenCrump!

 

[StephenCrump]

No problem, it was just a point in the right direction.

I'm glad you got your code working, and presumably it's very fast now.