VBA to copy range data with all visible and invisible cells to another sheet

[PritishS]

Dear Sirs,
Wish you all a Happy New Year!

After doing 2 days of searching I'm here for you kind help!

Requirement:

I want to copy a row range of data to other worksheet. For example- Range(A1:H1) from sheet1 to sheet2. Im am using this code below to do this

Sub CopyData()
Dim nextrow As Long, rngRow As Range, rfound As Range, sFind As String, Worksheet As Worksheet
nextrow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set rngRow = Range("A" & ActiveCell.Row & ":H" & ActiveCell.Row)
rngRow.Copy Destination:=Sheets("Sheet2").Range("A" & nextrow)

sFind = Range("A" & ActiveCell.Row).Value
             
With Sheets("Sheet2")
 Set rfound = .Columns(1).Find(What:=sFind, After:=.Cells(1, 1))
End With

Set rngRow = Nothing

End Sub

Problem Facing:
My problem is in this range A:H, D and F columns are hidden.
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]Pencil[/TD]
[TD]Inkpot[/TD]
[TD][/TD]
[TD]Ball[/TD]
[TD][/TD]
[TD]Bat[/TD]
[TD]Hat[/TD]
[/TR]
</tbody>[/TABLE]

With above mentioned code after pasting the same range in sheet2 is giving me result as shown below
Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]Pencil[/TD]
[TD]Inkpot[/TD]
[TD]Ball[/TD]
[TD]Bat[/TD]
[TD]Hat[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But requirement is

Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[/TR]
[TR]
[TD]Pen[/TD]
[TD]Pencil[/TD]
[TD]Inkpot[/TD]
[TD][/TD]
[TD]Bal[/TD]
[TD][/TD]
[TD]Bat[/TD]
[TD]Hat[/TD]
[/TR]
</tbody>[/TABLE]

Note:

Please note I can not unhide those cells, copy and paste then hide those cell. Because in actual case I have lots of column from A to CA and many are hidden columns and those are not fixed. So vba to unhide columns, copy paste and then hide is not applicable in this case.

Questions:

Is there any direct vba code like (xlcelltypevisible) which can be used to get the full range with hidden cells?

Thank you Very much in advance!!

Thanks & Regrads,
PritishS

 

[Fluff]

If you just want the visible cells copied try

Sub CopyData()
   Dim nextrow As Long, rngRow As Range, rfound As Range, sFind As String, Worksheet As Worksheet
   Dim rng As Range
   nextrow = Sheets("New").Cells(Rows.Count, 1).End(xlUp).Row + 1
   
   Set rngRow = Range("A" & ActiveCell.Row & ":H" & ActiveCell.Row)
   For Each rng In rngRow.SpecialCells(xlVisible).Areas
      rng.Copy Sheets("New").Cells(nextrow, rng.Column)
   Next rng
   
   sFind = Range("A" & ActiveCell.Row).Value
                
   With Sheets("New")
    Set rfound = .Columns(1).Find(What:=sFind, After:=.Cells(1, 1))
   End With
   
   Set rngRow = Nothing

End Sub

 

[PritishS]

"The world is Full with Good people and I always find them everywhere whenever in need!!".

Thank you Fluff Sir, Thank you very much. This code works exactly how I wanted. and moreover I learnt a new thing!!
Many you very much for saving my day!
Wish you a Happy and prosperous New Year!!

Thanks & Regards,
PritishS

 

[Fluff]

Glad to help & thanks for the feedback