Nothing happens when sub is executed

StarvingDog

New Member
Joined
May 28, 2019
Messages
8
I'm trying to be more concise with my code so I've attempted to set variables instead of activating sheets as I go. However, it appears as though nothing is happening after I hit execute. The point of this sub is to copy all rows with a blank value in column "O" from two worksheets and paste this information in a separate sheet in a different workbook.

Code:
Sub Transfer_OOS()


    Dim wb1 As Excel.Workbook
    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Open OOS.xlsm")
    Dim wb2 As Excel.Workbook
    Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Logs and Scorecard.xls*", Password:="98skv802kjsdf02")
    
    Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
    Set sht1 = wb1.Sheets("Open OOS")
    Set sht2 = wb2.Sheets("Chemistry OOS Log")
    Set sht3 = wb2.Sheets("Microbiology OOS Log")
    
    Dim c As Range
    
    Dim Last_Row As Long
    Last_Row = sht1.Range("A250").End(xlUp).Row
              
    For Each c In sht2.Range(("O2:O") & Cells(rows.Count, "O").End(xlUp).Row)
            If Not IsEmpty(c.Value) Then
            Else
                c.EntireRow.Copy
                sht1.Activate
                Cells(Last_Row + 1, 1).PasteSpecial xlValues
                Cells(Last_Row + 1, 1).PasteSpecial xlFormats
            End If
            Last_Row = Last_Row + 1
    Next c


    For Each c In sht3.Range(("O2:O") & Cells(rows.Count, "O").End(xlUp).Row)
            If Not IsEmpty(c.Value) Then
            Else
                c.EntireRow.Copy
                sht1.Activate
                Cells(Last_Row + 1, 1).PasteSpecial xlValues
                Cells(Last_Row + 1, 1).PasteSpecial xlFormats
            End If
            Last_Row = Last_Row + 1
    Next c
    
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try it like
Code:
   For Each c In Sht2.Range("O2:O" & Sht2.Cells(Rows.Count, "O").End(xlUp).Row)
            If c.Value = "" Then
                c.EntireRow.Copy
                Sht1.Cells(last_row + 1, 1).PasteSpecial xlValues
                Sht1.Cells(last_row + 1, 1).PasteSpecial xlFormats
                last_row = last_row + 1
            End If
    Next c
 
Upvote 0
Thank you for the response Fluff. I've made your suggested updates but to no avail. After hitting execute sht1 is pulled up and cell "O11" is selected, but nothing has been copied. I'm not receiving any error messages.

Code:
Sub Transfer_OOS()


    Dim wb1 As Excel.Workbook
    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Open OOS.xlsm")
    Dim wb2 As Excel.Workbook
    Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Logs and Scorecard.xls", Password:="98skv802kjsdf02")
    
    Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
    Set sht1 = wb1.Sheets("Open OOS")
    Set sht2 = wb2.Sheets("Chemistry OOS Log")
    Set sht3 = wb2.Sheets("Microbiology OOS Log")
    
    Dim c As Range
    
    Dim Last_Row As Long
    Last_Row = sht1.Range("A250").End(xlUp).Row
              
    For Each c In sht2.Range("O2:O" & sht2.Cells(rows.Count, "O").End(xlUp).Row)
            If c.Value = "" Then
                c.EntireRow.Copy
                sht1.Cells(Last_Row + 1, 1).PasteSpecial xlValues
                sht1.Cells(Last_Row + 1, 1).PasteSpecial xlFormats
                Last_Row = Last_Row + 1
            End If
    Next c
    
    For Each c In sht3.Range("O2:O" & sht3.Cells(rows.Count, "O").End(xlUp).Row)
            If c.Value = "" Then
                c.EntireRow.Copy
                sht1.Cells(Last_Row + 1, 1).PasteSpecial xlValues
                sht1.Cells(Last_Row + 1, 1).PasteSpecial xlFormats
                Last_Row = Last_Row + 1
            End If
    Next c
    
End Sub
 
Upvote 0
Are you sure that the cells in col O are actually blank & don't contain a space ?
 
Upvote 0
Does this work?
Code:
Option Explicit

Sub Transfer_OOS()
Dim wb1 As Excel.Workbook
Dim wb2 As Excel.Workbook
Dim sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Dim c As Range
Dim rngDst As Range

    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Open OOS.xlsm")

    Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Logs and Scorecard.xls*", Password:="98skv802kjsdf02")

    Set sht1 = wb1.Sheets("Open OOS")
    Set sht2 = wb2.Sheets("Chemistry OOS Log")
    Set sht3 = wb2.Sheets("Microbiology OOS Log")

    Set rngDst = sht1.Range("A" & Rows.Count).End(xlUp).Offset(1)

    For Each c In sht2.Range("O2:O" & sht2.Cells(Rows.Count, "O").End(xlUp).Row)

        If IsEmpty(c.Value) Then
            c.EntireRow.Copy
            rngDst.PasteSpecial xlValues
            rngDst.PasteSpecial xlFormats
            Set rngDst = rngDst.Offset(1)
        End If

    Next c

    For Each c In sht3.Range("O2:O" & sht3.Cells(Rows.Count, "O").End(xlUp).Row)

        If IsEmpty(c.Value) Then
            c.EntireRow.Copy
            rngDst.PasteSpecial xlValues
            rngDst.PasteSpecial xlFormats
            Set rngDst = rngDst.Offset(1)
        End If

    Next c

End Sub
 
Upvote 0
Thanks for the replies.

Fluff - I am sure. Previously, this sub was performing this task in a single workbook to a sheet called "Open OOS". For various reasons moving it to a separate workbook will be better. I thought it'd be easy to plug in the initial code and make a few adjustments, but apparently that's not the case.

Norie - Nope same thing as before.
 
Upvote 0
If you step through the code with F8 are the lines that copy ever executed?

Have you checked where the last value in column A in the destination sheet?
 
Upvote 0
First off, I did not know about F8... that's a huge help. Thank you. Second, I was unaware that set wb1 = Workbooks.Open(....) actually opens that workbook at that line of code. I've set wb1 to open the book that contains the code so it seems like when it gets to that line it reopens the book and restarts the sub. To clarify, when this workbook ("Open OOS") is opened I'd like it to copy data from two worksheets ("Chemistry OOS Log" and "Microbiology OOS Log") in another password protected workbook ("Logs and Scorecard") and paste it in this workbooks ("Open OOS") only worksheet ("Open OOS").
 
Upvote 0
If you want this code to run when a workbook is opened then you should put the code, or a call to it, in that workbook's Workbook_Open event, which you'll find in the ThisWorkbook module.

If you need to refer to the workbook the code is running from in the code then you can use ThisWorkbook.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top