Do I need to unhide all my rows before copying a range of cells?-CodePudding

I have a worksheet with hidden rows. I really can't believe the best way to go about this is to unhide and re-hide all my rows to copy and paste the contents to another sheet. Is it?

If I use this method:

forecastWs.Range("AC11:AC88").Copy chapterWs.Range("S12")

...nothing transfers over at all.

If I use select, as in:

'This is currently set to grab the Dec forecast Application.CutCopyMode = True

'forecastWs.Range("AC11:AC88").Copy chapterWs.Range("S12")

forecastWs.Range("AC11:AC88").Select
Selection.Copy
chapterWs.Paste Link:=True

...I get a "Select Class of Range has failed" error.

Sub FY23_Update_Chapter_Forecasts()
'
' Run a macro while making updates to NE 2023 file, and copy and paste the code into this sheet where it says to
'
Workbooks.Open ("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")

Dim i As Integer
Dim VarCellValue As String
Dim VarCellValue4 As String
Dim macroWb As Workbook
Dim macroWs As Worksheet
Dim forecasts As Workbook
Dim forecastWs As Worksheet
Dim chapterFile As Workbook
Dim chapterWs As Worksheet

Set macroWb = Workbooks.Open("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")
Set macroWs = macroWb.Sheets(1)

For i = macroWs.Range("A2").Value To macroWs.Range("C2").Value

VarCellValue = macroWs.Range("B" & i).Value
VarCellValue4 = macroWs.Range("F" & i).Value
    
    Application.DisplayAlerts = False
    
    Set forecasts = Workbooks.Open("S:\Finance\Budget & Forecast\2022\2022 Forecast\Chapter Forecasts\December Forecast\" & VarCellValue4 & ".xlsx")
    Debug.Print forecasts.FullName
    Set forecastWs = forecasts.Sheets(1)
    
    Set chapterFile = Workbooks.Open(macroWs.Range("A3").Value & VarCellValue & "\" & VarCellValue & ".xlsm")
    Set chapterWs = chapterFile.Sheets(1)
    
    'This is currently set to grab the Dec forecast
Application.CutCopyMode = True

'forecastWs.Range("AC11:AC88").Copy chapterWs.Range("S12")

forecastWs.Range("AC11:AC88").Select
Selection.Copy
chapterWs.Paste Link:=True
    Range("S8").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "December"
    Range("S9").Select
    Range("A1").Select

chapterFile.Save
chapterFile.Close
forecasts.Close
    
        Next i
    
    End Sub

CodePudding user response：

If you really need Copy&Paste, I fear you will need to unhide the rows. Copy&Paste in VBA/Excel does the same as when you do it manually in Excel.

However, if you only want to copy the values, you could use:

Dim r As Range
Set r = forecastWs.Range("AC11:AC88")
chapterWs.Range("S12").Resize(r.Rows.Count, r.Columns.Count).value = r.value

This will copy the values (and only the values, no formatting or whatever else) of the Range AC11:AC88 into VBA memory, no matter if the data is visible or not, and from memory to the destination range.