Disabling certain codes
Results 1 to 3 of 3

Thread: Disabling certain codes

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Disabling certain codes

    Hi experts and gurus,

    I have referenced the following code to call the workbook to open if not open and then copy data from one of the sheet and paste them in the sheet of another workbook (move or copy and check copy).

    Function Code:
    Code:
    Function checkFileIsOpen(chkSumFile As String) As Boolean
    
    On Error Resume Next
    checkFileIsOpen = (Workbooks(chkSumFile).Name = chkSumFile)
    
    On Error GoTo 0
    
    End Function
    Move or copy code:

    Code:
    Private Sub MoveOrCopy()
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim sh3 As Worksheet
    
    Application.DisplayAlerts = False
    
    Set wb1 = Workbooks("Germany.xlsx")
    Set wb2 = Workbooks("Canada.xlsm")
    Set sh3 = Worksheets("Ambience")
    
    Workbooks("Germany.xlsx").Sheets("Ambience").Range("A1:J2000").Copy _
        Destination:=Workbooks("Canada.xlsm").Sheets("Ambience").Range("A1")
    
    Application.DisplayAlerts = True
    
    End Sub
    Now I would want to disable these codes (both MoveOrCopy and the function codes) if the sheet in that another workbook contains data and is not empty. If the sheet in that another workbook is empty then run the codes.

    Thanks
    Roshan Shakya

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    1,281
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disabling certain codes

    try this function in an IF statement:
    Code:
    Function SheetIsBlank(ByVal wsh As Worksheet) As Boolean    On Error Resume Next
        Dim rng As Range
        Set rng = wsh.UsedRange
        If rng Is Nothing Then Exit Function
        
        Dim j As Long
        Err.Clear
        j = rng.SpecialCells(xlCellTypeConstants).Count
        j = j + rng.SpecialCells(xlCellTypeFormulas).Count
        SheetIsBlank = (j = 0) 'IsNull(i) '(Err.Number <> 0)
    
    
        Set rng = Nothing
    End Function
    something like:
    Code:
    if sheetisblank(workbooks(1).sheets(1)) then MoveOrCopy
    "...it's sad that in our blindness we gather thorns for flowers..."
    mostly using:
    windows 7 +10 (64-bit) / excel 2013 +2016 (32-bit) / access 2013 +2016 (32-bit) / some imagination & Google of course
    You don't need to read between the lines - just read them all!

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disabling certain codes

    Thank you bobsan42

    it worked.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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