Copy Range to other workbook on next empty row (only visible rows, include hidden columns)

**goomblar** · 02-11-2015, 03:59 AM

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!

**davesexcel** · 02-11-2015, 07:47 AM

You will probably have to loop through the range, for example.

Sub Button3_Click()
    Dim wb As Workbook, ws As Worksheet
    Dim bk As Workbook, BkSh As Worksheet, LstRw As Long
    Dim Rws As Long, Rng As Range, c As Range
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Your Worksheet")
    With ws
        Rws = .Cells(Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range(.Cells(9, 1), .Cells(Rws, 1)).SpecialCells(xlCellTypeVisible)
    End With
    Application.ScreenUpdating = 0
    Set bk = Workbooks.Open("C:\Users\Dave\Downloads\Book3(1).xlsx")
    Set BkSh = bk.Sheets("WorksheetA")
    For Each c In Rng.Cells
        LstRw = BkSh.Cells(Rows.Count, "A").End(xlUp).Row + 1
        c.Range("A1:M1").Copy Destination:=BkSh.Cells(LstRw, 1)
    Next c
    With bk
        .Save
        .Close
    End With

End Sub

Copy Range to other workbook on next empty row (only visible rows, include hidden columns)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View