Is there a way of pasting an array into a different workbook, while skipping Hidden Columns

[Coyotex3]

Hello guys, I have the following code, which I use to fill and then paste my array. This go around, I'm trying to paste the values into a worksheet("Upload Template") which is in another workbook. This specific worksheet has a lot of columns hidden. Is there a way I can make this code paste the data into the visible cells only?

Sub Testing()
Dim lr&, GL

lr = Range("A:R").SpecialCells(xlCellTypeLastCell).Row

GL = Range("A1:R" & lr).Value

Workbooks("Upload Template.xlsm").Activate

Range("E9").Resize(UBound(GL), UBound(GL, 2)).Value = GL


End Sub

Thank you.

 

[Coyotex3]

Hi Coyotex3,
So give the following one a try.

Sub Sample2()
    Dim GL, x As Long, y As Long, z As Long
    GL = Range("A1:R" & Range("A:R").SpecialCells(xlCellTypeLastCell).Row).Value
    Workbooks("Upload Template.xlsm").Activate
    With Range("E9")
        Do
            If .Offset(z).EntireRow.Hidden = False Then
                x = x + 1
                For y = LBound(GL, 2) To UBound(GL, 2)
                    .Cells(z + 1, y).Value = GL(x, y)
                Next
            End If
            z = z + 1
        Loop Until x = UBound(GL)
    End With
End Sub

@Colo San, I tried this one and this one is pasting the array into the hidden columns for some reason.

 

[Coyotex3]

Oops. If you want to give it another try change this line:

Set shtTemplate = wbGL.Worksheets(1)

To this line:

Set shtTemplate = wbTemplate.Worksheets(1)

That did the trick

 

[Colo]

@Colo San, I tried this one and this one is pasting the array into the hidden columns for some reason.

Sorry, the code that I made was for hidden "Rows". I ｍessed up again!

 

[Alex Blakenburg]

That did the trick

Thanks for letting us know. Glad we could help.

 

[Coyotex3]

Sorry, the code that I made was for hidden "Rows". I ｍessed up again!

Haha no worries! Alex modification happened to work and was able to do what I needed ! My job is partially automated at the moment thanks to you guys!

Arigatou Gozaimasu, Okiniawa ni modoritai desu!

 

[Coyotex3]

Thanks for letting us know. Glad we could help.

Thank you once again!!

 

[Alex Blakenburg]

Our pleasure.

 

[Coyotex3]

I went ahead and marked Alex's reply as the solution. Here is the code with the one modification:

Sub CopyToVisibleColumns()

Dim wbGL As Workbook, wbTemplate As Workbook
Dim shtGL As Worksheet, shtTemplate As Worksheet
Dim lrGL As Long, colGL As Long
Dim rngGL As Range, cellTemplate As Range
Dim arrGL As Variant
   
Application.ScreenUpdating = False
   
Set wbGL = ThisWorkbook
Set shtGL = wbGL.ActiveSheet
Set rngGL = shtGL.Range("A:R")
lrGL = rngGL.Find(What:="*" _
, Lookat:=xlPart _
, LookIn:=xlValues _
, searchorder:=xlByRows _
, searchdirection:=xlPrevious).Row
Set rngGL = rngGL.Resize(lrGL - 1).Offset(1)
arrGL = rngGL
   
Set wbTemplate = workbooks("Upload Template.xlsm")
Set shtTemplate = wbTemplate.Worksheets(1)
Set cellTemplate = shtTemplate.Range("E9").Resize(rngGL.Rows.Count)
   
For colGL = 1 To UBound(arrGL, 2)
       
Do Until Not cellTemplate.EntireColumn.Hidden
Set cellTemplate = cellTemplate.Offset(0, 1)
Loop
       
cellTemplate.Value = Application.Index(arrGL, 0, colGL)
Set cellTemplate = cellTemplate.Offset(0, 1)
       
 Next colGL

Application.ScreenUpdating = True
End Sub