How to improve the following code

[Kennis]

Hi, I am new to VBA Macros and I have completed my first VBA based on my work requirement. It works satisfactorily.
But I just want to know how to streamline my coding and remove any unnecessary code. Thanks.

Coding Sample:
>>> use code - tags <<<

Sub Assign_Workbook()

Dim ws1 As Worksheet, ws2 As Worksheet, last_row As Long
Dim ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Source")
Set ws2 = ThisWorkbook.Sheets("A")
Set ws3 = ThisWorkbook.Sheets("B")
Set ws4 = ThisWorkbook.Sheets("C")
Set ws5 = ThisWorkbook.Sheets("D")
Set ws6 = ThisWorkbook.Sheets("E")

'define last rows
last_row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
last_row3 = ws3.Range("A" & Rows.Count).End(xlUp).Row
last_row4 = ws4.Range("A" & Rows.Count).End(xlUp).Row
last_row5 = ws5.Range("A" & Rows.Count).End(xlUp).Row
last_row6 = ws6.Range("A" & Rows.Count).End(xlUp).Row

'Clear sheet
ws2.Cells.Clear
ws3.Cells.Clear
ws4.Cells.Clear
ws5.Cells.Clear
ws6.Cells.Clear

'Set first row data
ws1.Range("A1:K1").Copy ws2.Range("A1:K1")
ws1.Range("A1:K1").Copy ws3.Range("A1:K1")
ws1.Range("A1:K1").Copy ws4.Range("A1:K1")
ws1.Range("A1:K1").Copy ws5.Range("A1:K1")
ws1.Range("A1:K1").Copy ws6.Range("A1:K1")

'Copy rows
    For j = 1 To 500
        last_row2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
        last_row3 = ws3.Range("A" & Rows.Count).End(xlUp).Row
        last_row4 = ws4.Range("A" & Rows.Count).End(xlUp).Row
        last_row5 = ws5.Range("A" & Rows.Count).End(xlUp).Row
        last_row6 = ws6.Range("A" & Rows.Count).End(xlUp).Row
       
        'If A = Y
        If (ws1.Cells(j + 1, 13).Value = "Y") Then
           ws1.Range("A" & j + 1 & ":K" & j + 1).Copy ws2.Range("A" & last_row2 + 1)
        End If
       
        'If B = Y
        If (ws1.Cells(j + 1, 14).Value = "Y") Then
           ws1.Range("A" & j + 1 & ":K" & j + 1).Copy ws3.Range("A" & last_row3 + 1)
        End If
       
        'If C = Y
        If (ws1.Cells(j + 1, 15).Value = "Y") Then
           ws1.Range("A" & j + 1 & ":K" & j + 1).Copy ws4.Range("A" & last_row4 + 1)
        End If
       
        'If D = Y
        If (ws1.Cells(j + 1, 16).Value = "Y") Then
           ws1.Range("A" & j + 1 & ":K" & j + 1).Copy ws5.Range("A" & last_row5 + 1)
        End If
       
        'If E = Y
        If (ws1.Cells(j + 1, 17).Value = "Y") Then
           ws1.Range("A" & j + 1 & ":K" & j + 1).Copy ws6.Range("A" & last_row6 + 1)
        End If
    Next j
End Sub

 

[Marc L]

Hi, as it depends on the missing attachment …​

​

 

[Kennis]

Sorry, not understand. So I have to attach my file as well?

 

[Marc L]

If your actual code works as expected so there is nothing to improve so it depends also on what you really need …​

​

 

[Kennis]

Thank you for the reply.

E.g.
1. Take 'Set first row data as example

can I only copy the data once and paste it on 5 different sheets?

My current logic is to copy 5 times (Even though they are the same) and paste it separately.
>>> as noted <<<
>>> use code - tags <<<

ws1.Range("A1:K1").Copy ws2.Range("A1:K1")
ws1.Range("A1:K1").Copy ws3.Range("A1:K1")
ws1.Range("A1:K1").Copy ws4.Range("A1:K1")
ws1.Range("A1:K1").Copy ws5.Range("A1:K1")
ws1.Range("A1:K1").Copy ws6.Range("A1:K1")

2. Should it be better to use 5 for-loops to carry out the if-condition actions rather than use 1 only?

 

[vletm]

Kennis
Please,
before Your 1. ...
Is there any reason, for Your 'define last rows?

 

[Marc L]

2. Should it be better to use 5 for-loops to carry out the if-condition actions rather than use 1 only?

Not really better but shorter code obviously …​

If you want to loop so you can get rid of useless Worksheet variables or use a single Worksheet array variable.​

And you can get rid of any last_row variable if the worksheets structure is smart enough …​

​

As a reminder the best loop is to not loop !​

As a loop can be dramatically slower than Excel basics like a filter / advanced filter for example.​

So according to these Excel basics your actual code should be trashed and restarted from blank to write an efficient VBA process …​

​

Before to code anything the first question must be « How Excel can help me to solve my need ? »​

Like any Excel user operating manually so replicating the same operations​

the VBA procedure should be better than any poor row by row looping process …​

​