Copy Visible Cells Only

[ajc5382]

Hello All - first time using VBA in a while.

I have some pretty basic code I need to tweak to select the visible cells in a range. If it's not asking for too much, I'd also like to make this range dynamic (the number of rows is the only thing that varies, number of columns will be constant).

Private Sub CopyItOver()
  Set Newbook = Workbooks.Add
  ThisWorkbook.Worksheets("Proposal Tracker").Range("A15:CQ19").Copy
  Newbook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
End Sub

 

[dangelor]

Possibly...

Private Sub CopyItOver()
  Set Newbook = Workbooks.Add
  ThisWorkbook.Worksheets("Proposal Tracker").Range("A15:CQ19").SpecialCells(xlCellTypeVisible).Copy
  Newbook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
End Sub

 

[ajc5382]

Thanks for the reply. That seemed like an easy fix so I tried it prior, came back as a Run-time error 1004, Application=defined or object-defined error.

 

[dangelor]

Not entirely sure, but I believe when a workbook is created, it becomes 'ThisWorkbook', and a new workbook would not have the referenced worksheet. Try changing 'ThisWorkbook' to reference the actual workbook name.

 

[ajc5382]

Tried the following, but no dice. Perhaps the way I'm referencing my workbook is incorrect, or something with the name?​

Private Sub CopyItOver()

 Set Newbook = Workbooks.Add
 Workbooks("Copy of SMD Prosposal Tracker_1.5.17 R1.xlsm").Worksheets("Proposal Tracker").Cells.SpecialCells (xlCellTypeVisible).Range("A15:CQ19").Copy
 Newbook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues) 

End Sub

 

[dangelor]

Let's try a different tack...

Private Sub CopyItOver()
    Dim vPasteValues As Variant
    With Workbooks("Copy of SMD Prosposal Tracker_1.5.17 R1.xlsm").Worksheets("Proposal Tracker")
        vPasteValues = .Range("A15:CQ19").SpecialCells(xlCellTypeVisible)
    End With
    Set Newbook = Workbooks.Add
    Newbook.Worksheets("Sheet1").Range("A1") _
    .Resize(UBound(vPasteValues, 1), UBound(vPasteValues, 2)) = vPasteValues
End Sub

 

[tonyyy]

How 'bout...

Private Sub CopyItOver()
Dim Newbook As Workbook, ws As Worksheet
Set Newbook = Workbooks.Add
Set ws = ThisWorkbook.Worksheets("Proposal Tracker")
ws.Range("A15:CQ" & ws.Cells(Rows.Count, "CQ").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Newbook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

Cheers,

tonyyy

 

[ajc5382]

How 'bout...

Private Sub CopyItOver()
Dim Newbook As Workbook, ws As Worksheet
Set Newbook = Workbooks.Add
Set ws = ThisWorkbook.Worksheets("Proposal Tracker")
ws.Range("A15:CQ" & ws.Cells(Rows.Count, "CQ").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Newbook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

Cheers,

tonyyy

Tony - worked great, much appreciated! Hope you guys get a little sunshine out there sometime soon.

I have one last question. I'm using the following code to open the SaveAs box after the code provided above....however it will only save as a file type "file" vs xlsx or xlsm. Can somebody point me to where I've got this wrong.

Dim workbook_Name as Variant
    workbook_Name = Application.GetSaveAsFilename
    If workbook_Name <> False Then
        ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=52
    End If
End Sub

 

[tonyyy]

You might try...

ActiveWorkbook.SaveAs Filename:=workbook_Name & ".xlsx", FileFormat:=52

 

[dangelor]

Try adding the file filter argument...

Dim workbook_Name As Variant
    workbook_Name = Application.GetSaveAsFilename(fileFilter:="Macro Enabled Files (*.xlsm), *.xlsm")
    If workbook_Name <> False Then
        ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=52
    End If