VBA Code to Copy Row (without hiddin coloums) based on an Empty Cell in Column V from one worksheet to Another Sheet

[tcopeland]

I inherited an excel file that has thousands of rows in one sheet and I have been asked to create a new sheet that only has specific coloumns (other coloumns have been hidden) and rows when there is a blank in Coloumn V. The names of the worksheets are Finalized Ks and Pending Ks. Any help would be appreciated. Thank you.

 

[JoeMo]

This is untested so may need some tweaking. Assumes the source and destination sheets already exist and the pertinent columns to exclude on the source sheet are already hidden.

Sub tcopeland()
'run this code when sheet 'Finalized Ks' is the activesheet
Application.ScreenUpdating = False
On Error Resume Next
Sheets("Finalized Ks").UsedRange.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Pending Ks").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

 

[tcopeland]

Re: VBA Code to Copy Row (without hidden coloums) based on an Empty Cell in Column V from one worksheet to Another Sheet

Joe,

Thank you. That worked to copy all the rows, not just the blank ones, but it did only copy the visible coloumns. Is there a way to limit to just the rows with contain no information in coloumn v?

Thank you again,

Tana

 

[JoeMo]

Re: VBA Code to Copy Row (without hidden coloums) based on an Empty Cell in Column V from one worksheet to Another Sheet

Joe,

Thank you. That worked to copy all the rows, not just the blank ones, but it did only copy the visible coloumns. Is there a way to limit to just the rows with contain no information in coloumn v?

Thank you again,

Tana

You are welcome. Guess I didn't understand the col V impact - thought that was linked to which columns you hide. Assuming thos col V cells are truly blank (not having formulas that return an empty string), this should do what I think you want:

Sub tcopeland()
Application.ScreenUpdating = False
On Error Resume Next
Intersect(Sheets("Finalized Ks").UsedRange, _
    Columns("V")).SpecialCells(xlCellTypeBlanks).EntireRow.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Pending Ks").Range("A1")
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Copy the code directly from your browser and overwrite the earlier code I posted with a paste.

 

[tcopeland]

Re: VBA Code to Copy Row (without hidden coloums) based on an Empty Cell in Column V from one worksheet to Another Sheet

PERFECT!!!!

Thank you so much, that saved me hours of time.

 

[JoeMo]

Re: VBA Code to Copy Row (without hidden coloums) based on an Empty Cell in Column V from one worksheet to Another Sheet

PERFECT!!!!

Thank you so much, that saved me hours of time.

You are welcome - thanks for the reply.