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.

 

[Colo]

Hi Coyotex3,
Getting values as an array in a range with hidden rows or filtered needs one more step. If there is not much data, the easier one is to copy all to a helper worksheet and then put a range in an array. The second one is preparing an array then looping through values in visible range then putting those in an array. Here is a sample of the second one. Executing speed is a little bit faster than the first one.

Sub Sample1()

    Dim lr&, GL
    Dim c As Range, x As Long, y As Long

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

    With Range("A1:R" & lr)
        'prepare an array
        ReDim GL(1 To .Resize(, 1).SpecialCells(xlCellTypeVisible).Count, 1 To 18)

        'Loop through rows of visible cells then put value into an array
        For Each c In .SpecialCells(xlCellTypeVisible).Rows
            x = x + 1
            For y = 1 To 18
                GL(x, y) = c.Cells(1, y)
            Next
        Next
    End With

    Workbooks("Upload Template.xlsm").Activate
    Range("E9").Resize(UBound(GL), UBound(GL, 2)).Value = GL

End Sub

 

[Coyotex3]

Hi Coyotex3,
Getting values as an array in a range with hidden rows or filtered needs one more step. If there is not much data, the easier one is to copy all to a helper worksheet and then put a range in an array. The second one is preparing an array then looping through values in visible range then putting those in an array. Here is a sample of the second one. Executing speed is a little bit faster than the first one.

Sub Sample1()

    Dim lr&, GL
    Dim c As Range, x As Long, y As Long

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

    With Range("A1:R" & lr)
        'prepare an array
        ReDim GL(1 To .Resize(, 1).SpecialCells(xlCellTypeVisible).Count, 1 To 18)

        'Loop through rows of visible cells then put value into an array
        For Each c In .SpecialCells(xlCellTypeVisible).Rows
            x = x + 1
            For y = 1 To 18
                GL(x, y) = c.Cells(1, y)
            Next
        Next
    End With

    Workbooks("Upload Template.xlsm").Activate
    Range("E9").Resize(UBound(GL), UBound(GL, 2)).Value = GL

End Sub

Colo San, for this one I would like to "Paste" the array into "Workbooks("Upload Template.xlsm"). The sheet "Upload Template.xlsm" has hidden columns. I would like to paste this array in the visible columns only, if possible

 

[Alex Blakenburg]

See if this works for you:

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 = wbGL.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

 

[Colo]

The sheet "Upload Template.xlsm" has hidden columns.

Oh, I made a mistake!

 

[Coyotex3]

See if this works for you:

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 = wbGL.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

Hi, Alex, This one is not working. This one is dumping the array into the activesheet.

 

[Coyotex3]

Hi, Alex, This one is not working. This one is dumping the array into the activesheet.

Disregard that statement. The code is not doing such a thing. One second why I run it again! Sorry.

 

[Coyotex3]

@Alex Blakenburg, Sorry I misspoke when I said it was dumping the array into the activesheet. It seems like the code is not doing anything.

 

[Colo]

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

 

[Alex Blakenburg]

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)