0

Can I hel me to modify this scritp to copy only not empty rows in a new worksheet ?

Sub Export_Basecosti()

Dim fd As Office.FileDialog
Dim fn As String
Dim FilePath, FileOnly, PathOnly As String
Dim ur As Long


FileOnly = ThisWorkbook.Name


    Set fd = Application.FileDialog(msoFileDialogFilePicker)


   With fd

      .AllowMultiSelect = False

      .Title = "Scegli il file della Base Costi da Compilare"

      .Filters.Clear
      .Filters.Add "All Files", "*.*"

      If .Show = True Then
      
       fn = Empty
       fn = Dir(fd.SelectedItems(1))

      End If
      
      
   End With
   
    Windows(FileOnly).Activate
    Sheets("Base Costi").Select
    
    ur = Worksheets("Base Costi").Cells(Rows.Count, "C").End(xlUp).Row
    
    MsgBox ur
    
    'Copy A Range of Data
     Worksheets("Base Costi").Rows("2:" & ur).Copy
    ' Rows("2:252").Select
    ' Selection.Copy
    ' Selection.SpecialCells(xlCellTypeVisible).Copy
    
    
    
   
    Workbooks.Open (fn)
    
    Sheets("Bid COSTS - Link").Select
    
    'Clear
    Sheets("Bid COSTS - Link").Rows("8:508").ClearContents
    
    ' Range("A8").Select
    ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Bid COSTS - Link").Range("A8").PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
    
    ' ActiveWorkbook.CheckCompatibility = False
    ' ActiveWorkbook.Save
    
    'Dim ws As Worksheet
    'Set ws = ThisWorkbook.Sheets("Bid COSTS - Link")
    
    'ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    Workbooks(fn).Save
    Workbooks(fn).Close
    
    MsgBox "Base Costi Copiata"
    
End Sub

i have tried to modify the code, but don't work..

freeflow
  • 4,129
  • 3
  • 10
  • 18
blasfemo
  • 1
  • 1
    Maybe try to change your code from `Worksheets("Base Costi").Rows("2:" & ur).Copy` into `Worksheets("Base Costi").range("C2",range("C" & ur)).specialcells(xlconstants).entirerow.copy` ? – karma Dec 12 '22 at 12:47
  • 1
    Couple of things. **1.** [Avoid the use of .Select/.Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Work with objects **2.** You are copying and then doing something and then pasting. Excel has a habit of clearing the clipboard. I recommend reading [Why does PasteSpecial method sometimes throw error 1004?](https://stackoverflow.com/questions/64872887/why-does-pastespecial-method-sometimes-throw-error-1004) **3.** Identify your range and then loop through them. Use `Application.WorksheetFunction.CountA()` to identify the blank rows & exclude them – Siddharth Rout Dec 12 '22 at 15:15

0 Answers0