Hi Excel Forum,

I have a Workbook1 which contains multiple hidden rows & multiple hidden columns. I currently have a code to copy my specified range to Workbook2, but so far I can only either copy everything (i.e. all hidden/non-hidden) or just visible. The problem is that I only want to copy visible rows while still copying all the hidden columns.

Here is my code so far, which copy/pastes everything in the range. (Please note the top half of the code is not relevant, but I kept it here to avoid misunderstanding.)


Option Explicit

Sub SaveCopyS()
'The following code is not relevant for this topic. Please look down below to the next comment.

Sheets("Worksheet1").Protect Password:="aaa", UserInterFaceOnly:=True

If MsgBox("A copy of this worksheet will be created by using the date in cell B4. If a file with this date already exists, it will be overwritten. Are you sure you want to proceed?", vbQuestion + vbYesNo) <> vbYes Then
  
    Exit Sub
  
  End If
  
  MsgBox "This version will now be uploaded to the Team Site."


Dim FileName As String
Dim Path As String

Application.DisplayAlerts = False



Sheets("W").Copy
    Sheets("Worksheet1").Name = "Static Copy"
    ActiveSheet.UsedRange.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Path = "C:\PersonalFileLocations\"
    FileName = Range("B4").Value & ".xlsx"
    
    ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbook, CreateBackup:=False
    
Application.DisplayAlerts = True

'The code below is relevant for this thread:
Dim cell As Range
Application.ScreenUpdating = False
Range("A9:M48").Select
Selection.Copy
Workbooks.Open ("Workbook2.xlsx")
Sheets("WorksheetA").Activate

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Application.ScreenUpdating = True


End Sub

Thanks for any tips/advice/help!