VBA Copy and Paste issues - skips blank cells unintentionally.

[apatheticrory]

Hi,

I've created a workbook that when requested, copies data and pastes it into other workbooks. When pasting, i want it to go to the next available blank row - which it does great on the first sheet it opens, but the second it skips to row 120 odd and pastes it down there, even though everything before it is blank?

Would be hugely grateful if someone could point out where i'm going wrong.....

Sub Submit()


    Workbooks.Open "FILEPATH.FILENAME", WriteResPassword:="PORTER"
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "PortER is being accessed by another user. Please retry in a few seconds.....", vbExclamation, "PortER"
        ActiveWindow.Close
        Exit Sub
      
              Else
                Workbooks("PortER Submission.xlsm").Activate
                Sheets("PORTER Copy").Visible = True
                Worksheets("PORTER Copy").Activate
                Worksheets("PORTER Copy").Unprotect Password:="PortER_Int"
              
                Range("A2:AS2").Copy
                
                
                Sheets("PORTER Copy").Visible = False
                Workbooks("PortER 2017-18.xlsm").Activate
                Worksheets("PORTER").Activate
                Range("A2").Activate
                
                
Start:
        If IsEmpty(ActiveCell.Offset(RowNo, 0)) Then
            ActiveCell.Offset(RowNo, 0).PasteSpecial xlPasteValues
        Else
            RowNo = RowNo + 1
            GoTo Start
            End If
            Range("A1").Select
            End If
        Workbooks("PortER 2017-18.xlsm").Close Savechanges:=True
        Workbooks("PortER Submission.xlsm").Activate
        Worksheets("PORTER Copy").Protect Password:="PortER_Int"
        
        'RCC Return
        
        Workbooks.Open "FILEPATH/FILENAME.xlsm"
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "PortER is being accessed by another user. Please retry in a few seconds.....", vbExclamation, "PortER"
        ActiveWindow.Close
        Exit Sub
      
              Else
                Workbooks("PortER Submission.xlsm").Activate
                Sheets("PORTER Copy").Visible = True
                Worksheets("PORTER Copy").Activate
                Worksheets("PORTER Copy").Unprotect Password:="PortER_Int"
              
                Range("A2,B2,E2,I2,L2,M2,N2,O2,R2,S2,T2,U2,V2,X2,Y2,Z2,AB2,AI2").Copy
                
                
                Sheets("PORTER Copy").Visible = False
                Workbooks("RCC PORTER Return.xlsm").Activate
                Worksheets("Data").Activate
                Range("A2").Activate
                  
                


        If IsEmpty(ActiveCell.Offset(RowNo, 0)) Then
            ActiveCell.Offset(RowNo, 0).PasteSpecial xlPasteValues
        Else
            RowNo = RowNo + 1
            GoTo Start
            End If
            Range("A1").Select
            End If
        Workbooks("RCC PORTER Return.xlsm").Close Savechanges:=True
        Workbooks("PortER Submission.xlsm").Activate
        Worksheets("PORTER Copy").Protect Password:="PortER_Int"
        
        
        


Sheets("PORTER").Select
Range("E10,E12,E14,E16,E18,E20,E22,E24,E26,E30,E32,E34,E36,E42,E44,K10,K12,K14,K16,K18,K20,K22,K24,K26,K28,K30,K32,K34,K36,K38,K40,K42,K44,Q10,Q12,Q14,Q16,Q20,Q22,Q24,Q26,Q28").Value = ""
Range("E10").Select


MsgBox "Your PortER submission was successful, Thank you. Should any amendments to your submission be required, please email.", vbInformation, "PortER"


Sheets("Home").Select
Sheets("PORTER").Visible = False




End Sub

 

[Fluff]

Hi & welcome to the board
This is untested but try

Sub Submit()


    Workbooks.Open "FILEPATH.FILENAME", WriteResPassword:="PORTER"
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "PortER is being accessed by another user. Please retry in a few seconds.....", vbExclamation, "PortER"
        ActiveWindow.Close
        Exit Sub
      
              Else
                Workbooks("PortER Submission.xlsm").Activate
                Sheets("PORTER Copy").Visible = True
                Worksheets("PORTER Copy").Activate
                Worksheets("PORTER Copy").Unprotect Password:="PortER_Int"
              
                Range("A2:AS2").Copy
                
                
                Sheets("PORTER Copy").Visible = False
                Workbooks("PortER 2017-18.xlsm").Activate
                Worksheets("PORTER").Activate
                Range("A2").Activate
                
                
Start:
        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Range("A1").Select
        End If
        Workbooks("PortER 2017-18.xlsm").Close Savechanges:=True
        Workbooks("PortER Submission.xlsm").Activate
        Worksheets("PORTER Copy").Protect Password:="PortER_Int"
        
        'RCC Return
        
        Workbooks.Open "FILEPATH/FILENAME.xlsm"
    If ActiveWorkbook.ReadOnly = True Then
        MsgBox "PortER is being accessed by another user. Please retry in a few seconds.....", vbExclamation, "PortER"
        ActiveWindow.Close
        Exit Sub
      
              Else
                Workbooks("PortER Submission.xlsm").Activate
                Sheets("PORTER Copy").Visible = True
                Worksheets("PORTER Copy").Activate
                Worksheets("PORTER Copy").Unprotect Password:="PortER_Int"
              
                Range("A2,B2,E2,I2,L2,M2,N2,O2,R2,S2,T2,U2,V2,X2,Y2,Z2,AB2,AI2").Copy
                
                
                Sheets("PORTER Copy").Visible = False
                Workbooks("RCC PORTER Return.xlsm").Activate
                Worksheets("Data").Activate
                Range("A2").Activate
                  
                


        Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Range("A1").Select
        End If
        Workbooks("RCC PORTER Return.xlsm").Close Savechanges:=True
        Workbooks("PortER Submission.xlsm").Activate
        Worksheets("PORTER Copy").Protect Password:="PortER_Int"
        
        
        


Sheets("PORTER").Select
Range("E10,E12,E14,E16,E18,E20,E22,E24,E26,E30,E32,E34,E36,E42,E44,K10,K12,K14,K16,K18,K20,K22,K24,K26,K28,K30,K32,K34,K36,K38,K40,K42,K44,Q10,Q12,Q14,Q16,Q20,Q22,Q24,Q26,Q28").Value = ""
Range("E10").Select


MsgBox "Your PortER submission was successful, Thank you. Should any amendments to your submission be required, please email.", vbInformation, "PortER"


Sheets("Home").Select
Sheets("PORTER").Visible = False




End Sub

 

[apatheticrory]

Worked perfectly. Thank you SO much, I really appreciate it

 

[Fluff]

Glad to help & thanks for the feedback