+ Reply to Thread
Results 1 to 6 of 6

Copy non empty cells form sheet to sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Copy non empty cells form sheet to sheet

    I want to copy non emty cells from a sheet to another sheet.
    This is what i have so far. But is is not working.

    Sub LAB_Downloaden()
    
    Application.ScreenUpdating = False
    
    
    Workbooks.Open "C:\Users\berry\Google Drive\PKB 2D\PKB data bestanden\LAB_data.xlsx"
    
    Set shI = Workbooks("LAB_data.xlsx").Worksheets("Invulblad")
    Set shP = Sheets("Invulblad")
    
        shI.Range("$D$9:$J$129").AutoFilter Field:=2, Criteria1:=Array( _
            "EXCEL", "HOP", "PGIM"), Operator:=xlFilterValues
            
        shI.Range(Cells(10, 13), Cells(shI.Cells(Rows.Count, 3).End(xlUp).Row, 20)) = ""
        
        On Error Resume Next
            shI.ShowAllData
            shP.ShowAllData
        On Error GoTo 0
    
     shI.Range(Cells(10, 13), Cells(shI.Cells(Rows.Count, 3).End(xlUp).Row, 20)).copy
     shP.Cells(10, 13).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, Transpose:=False
        
     
     Application.ScreenUpdating = True
    
    End Sub

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Copy non empty cells form sheet to sheet

    After these lines:

    Workbooks.Open "C:\Users\berry\Google Drive\PKB 2D\PKB data bestanden\LAB_data.xlsx"
    
    Set shI = Workbooks("LAB_data.xlsx").Worksheets("Invulblad")
    Set shP = Sheets("Invulblad")
    I suspect shI and shP both reference the same sheet. Opening a workbook will set it as the active one and Sheets("Invulblad") will pick the sheet name Invulblad from the active workbook. I can't validate this for certain but try putting the lines in a different order:

    Set shP = Sheets("Invulblad")
    Workbooks.Open "C:\Users\berry\Google Drive\PKB 2D\PKB data bestanden\LAB_data.xlsx"
    Set shI = Workbooks("LAB_data.xlsx").Worksheets("Invulblad")
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Copy non empty cells form sheet to sheet

    I think that you are write. I use a script that saves a sheet so that another sheet can downloads it. Is there a way to save a sheet in another name? Now it copy with the same name.

    Sub LAB_data_opslaan()
    
        Application.DisplayAlerts = False
        Dim wb As Workbook
        Set wb = Workbooks.Add
        ThisWorkbook.Sheets("Invulblad").copy Before:=wb.Sheets(1)
        wb.SaveAs "C:\Users\berry\Google Drive\PKB 2D\PKB data bestanden\LAB_data.xlsx"
        Workbooks("LAB_data.XLSX").Close
        Application.DisplayAlerts = True
        
    End Sub

  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Copy non empty cells form sheet to sheet

    Once you execute this line:

    ThisWorkbook.Sheets("Invulblad").copy Before:=wb.Sheets(1)
    Your copied sheet will be at position 1 so you can then use:

    Sheets(1).Name = "Whatever"
    WBD

  5. #5
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    Re: Copy non empty cells form sheet to sheet

    It is working fine. But the script also copy's formula's.
    Is there a way to copy only values? No formulas buttons or whatever!
        Application.DisplayAlerts = False
        Dim wb As Workbook
        Set wb = Workbooks.Add
        ThisWorkbook.Sheets("Invulblad").copy Before:=wb.Sheets(1)
        Sheets(1).Name = "LAB_Invulblad"
        wb.SaveAs "C:\Users\berry\Google Drive\PKB 2D\PKB data bestanden\LAB_data.xlsx"
        Workbooks("LAB_data.XLSX").Close
        Application.DisplayAlerts = True

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Copy non empty cells form sheet to sheet

    You can remove formulas from a sheet like this:

    Sheets(1).Cells.Value = Sheets(1).Cells.Value
    WBD

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-01-2017, 04:12 PM
  2. [SOLVED] Copy cells to last empty row of column in another sheet.
    By drgkt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-22-2017, 06:20 PM
  3. [SOLVED] Copy and Paste only filled cells from a dynamic range to another sheet in empty cells
    By seanpcorbett1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-12-2016, 03:23 PM
  4. [SOLVED] Problems With Macro to Copy Cells From One Sheet to Next Empty Row In Another Sheet
    By sliesneekr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2014, 09:12 AM
  5. [SOLVED] Copy data from one sheet (multiple cells) to another sheet in tabular form
    By dvs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2014, 12:13 PM
  6. Replies: 1
    Last Post: 04-22-2013, 10:34 AM
  7. Copy Multiple Cells to Next empty Cell on New Sheet
    By pk2356 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2010, 10:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1