VBA copy and paste visible cells

mjlh

New Member
Joined
Jul 23, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi all,
I am trying to copy and paste cells in my workbooks into the master excluding those rows that are hidden. I tried using the special cells code but it only copied the formats of the cells and not the contents. Am i missing something?

My code is as follows:
VBA Code:
Sub ImportSuccessionData()
     
    Dim Path            As String 'string variable to hold the path to look through
    Dim FileName        As String 'temporary filename string variable
    Dim tWB             As Workbook 'temporary workbook (each in directory)
    Dim tWS             As Worksheet 'temporary worksheet variable
    Dim mWB             As Workbook 'master workbook
    Dim aWS             As Worksheet 'active sheet in master workbook
    Dim RowCount        As Long 'Rows used on master sheet
    Dim uRange          As Range 'usedrange for each temporary sheet
    Dim LastRowUsed     As Long
    Dim destrange       As Range
    Dim calcmode        As Long

   
       '***** Set folder to cycle through *****
 
    Application.ScreenUpdating = False
 
 
 With Application.FileDialog(msoFileDialogFolderPicker) 'Dialogue to select folder wtih files
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      Path = .SelectedItems(1)
      Err.Clear
      On Error GoTo 0
    End With
 
 Application.EnableEvents = False 'turn off events
    Application.ScreenUpdating = False 'turn off screen updating
    Set mWB = ActiveWorkbook 'select workbook variable as open file
    mWB.Sheets("Succession Plan").Select 'select worksheet to copy data into
    Set aWS = mWB.ActiveSheet 'set active sheet variable to data sheet
    LastRowUsed = aWS.Cells(aWS.Rows.Count, "A").End(xlUp).Row
 
 
    If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
        Path = Path & Application.PathSeparator 'add "\"
    
    FileName = dir(Path & "*.xl*", vbNormal) 'set first file's name to filename variable
    Do Until FileName = "" 'loop until all files have been parsed
        If Path <> ThisWorkbook.Path Or FileName <> ThisWorkbook.Name Then
            Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
             ActiveWorkbook.Unprotect Password:="star2020"
            Set tWS = tWB.Sheets("Succession Plan")
                Set uRange = tWS.Range("A1:T30") 'set used range
                If RowCount + uRange.Rows.Count > 65536 Then 'if the used range wont fit on the sheet
                    aWS.Columns.AutoFit 'autofit mostly-used worksheet's columns
                    Set aWS = mWB.Sheets.Add(after:=aWS) 'add a new sheet that will accommodate data
                    RowCount = 0 'reset RowCount variable
                End If

                    uRange.SpecialCells(xlCellTypeVisible).Copy
               
    Set destrange = aWS.Range("A" & RowCount + LastRowUsed)
                         
                         
With destrange
    .PasteSpecial xlPasteValuesAndNumberFormats
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteColumnWidths
     Application.CutCopyMode = False
End With
                                    
        RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
                    
        tWB.Close False 'close temporary workbook without saving
        End If
        FileName = dir() 'set next file's name to FileName variable
    Loop

   
   mWB.Sheets("Succession Plan").Select 'select fourth data sheet on master workbook
     'aWS.Protect ("9a9b9c")
     Application.EnableEvents = True 're-enable events
    Application.ScreenUpdating = True 'turn screen updating back on
     

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
     .Calculation = xlAutomatic
     


    End With
    
       
      End If
      

End Sub
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
Hello! What about
VBA Code:
With destrange
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With
 

mjlh

New Member
Joined
Jul 23, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello! What about
VBA Code:
With destrange
    .PasteSpecial xlPasteColumnWidths
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

This worked perfectly! Would you be able to explain the reason? Was this due to the Application.CutCopyMode?

Thanks!
 

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
156
Office Version
  1. 2010
Platform
  1. Windows
Glad to help and thanks for the feedback.
Was this due to the Application.CutCopyMode?
Nope. String
VBA Code:
Application.CutCopyMode = False
removes "marching ants" after copy-paste.

The order of the operators .PasteSpecial is important here.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,971
Messages
5,621,890
Members
415,864
Latest member
cybid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top