VBA copy a row if empty copy another row

June 27, 2017 at 20:53:48
Specs: Windows 7
Hi there,

I want to copy from Raw data sheet to Detail list sheet as below. I am not sure why the code is not working.. can anyone guide me please...

I want to copy from "Raw data" sheet

	
	AS	                 AU
1	customer code	      vendor code
2		              vendor code 1
3	customer code1	
4	customer code2	
5	customer code3	
6		               vendor code 2
7		               vendor code 3
8	customer code4	
9	customer code5	
10	customer code6	
11		              vendor code 4

to "Detail list" sheet as below

	
              K
1	
2	   code
3	vendor code 1
4	customer code1
5	customer code2
6	customer code3
7	vendor code 2
8	vendor code 3
9	customer code4
10	customer code5
11	customer code6
12	vendor code 4

I wrote this.... but it's not returning the result as I wanted. It only copy the column AU.. not AS

If Sheets("Raw data").Range("AS2").Value = "" Then

Sheets("Raw data").Range("AU2", Sheets("Raw data").Range("AU" & Rows.Count).End(xlUp)).
Copy Destination:=Sheets("Detail list").Range("K3")

Else
Sheets("Raw data").Range("AS2", Sheets("Raw data").Range("AS" & Rows.Count).End(xlUp)).
Copy Destination:=Sheets("Detail list").Range("K3")

End If

message edited by suzysss


See More: VBA copy a row if empty copy another row

Reply ↓  Report •

#1
June 28, 2017 at 00:59:28
Couple of questions

on your 'Raw Data' sheet, AS1 has 'Customer Code' and AU1 has 'Vendor Code'

what happen to these values they were not moved to 'Details Sheet' - is this correct?

on your 'Details List' K2 has 'Code' in it, where did this come from?

it looks like all you want to do is merge the two column AS and AU filling the blanks in AS with the adjacent value in AU, then copy that to the details sheet, Column K from Row 2? onwards?


Reply ↓  Report •

#2
June 28, 2017 at 01:06:29
yes I want to copy "raw data" sheet column AS and "raw data" sheet column AU to "detail list" sheet column K. if AS is blank then fill K with AU. If AU is blank fill K with AS.. like this..

K2 "code" is just I typed it in.. the combination should start from row 3 I meant..

message edited by suzysss


Reply ↓  Report •

#3
June 28, 2017 at 02:29:33
Ok, this solution is a little messy, I don't have much time to play so please try this

Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    MaxValue = Application.Max(Sheet1.Range("AS" & Rows.Count).End(xlUp).Row, Sheet1.Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Range("AS1", Range("AS" & MaxValue))
        
        If bCell <> Empty Then

            If Sheets("Details List").Range("K3") = vbNullString Then
                Sheets("Details List").Range("K3") = bCell
            Else
                nr = Sheets("Details List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Details List").Range("K" & nr + 1) = bCell
            End If
            
        Else
                nr = Sheets("Details List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Details List").Range("K" & nr + 1) = bCell.Offset(0, 2)
        End If
    
    Next bCell
    
    Sheets("Details List").Columns("K:K").AutoFit

End Sub

message edited by AlwaysWillingToLearn


Reply ↓  Report •

Related Solutions

#4
June 28, 2017 at 03:28:21
run time error T.T

Reply ↓  Report •

#5
June 28, 2017 at 04:33:56
Below seems work but it copied to "Details List" K2 instead of K3... and it is very slow...

Dim i As Long
Dim toprow As Long
toprow = 2

For i = toprow To Sheets("raw data").Cells(Rows.Count, 1).End(xlUp).Row
If Sheets("raw data").Cells(i, 45) = "" Then
    Sheets("raw data").Cells(i, 47).Copy Sheets("detail list").Cells(i, 11)
Else: Sheets("raw data").Cells(i, 45).Copy Sheets("detail list").Cells(i, 11)

End If
Next i


Reply ↓  Report •

#6
June 28, 2017 at 05:23:54
Where does the runtime error occur?

Please check the names of your sheets and update the code accordingly

I have 'Raw Data'

and 'Details List'

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#7
June 28, 2017 at 05:34:20
Below bold part is wrong.


Sub CopyToDL()

Dim iString As String
Dim bCell As Range
Dim MaxValue As Long

iString = vbNullString

MaxValue = Application.Max(Sheet1.Range("AS" & Rows.Count).End(xlUp).Row, Sheet1.Range("AU" & Rows.Count).End(xlUp).Row)

For Each bCell In Range("AS1", Range("AS" & MaxValue))

If bCell <> Empty Then

If Sheets("Details List").Range("K3") = vbNullString Then
Sheets("Details List").Range("K3") = bCell
Else
nr = Sheets("Details List").Range("K" & Rows.Count).End(xlUp).Row
Sheets("Details List").Range("K" & nr + 1) = bCell
End If

Else
nr = Sheets("Details List").Range("K" & Rows.Count).End(xlUp).Row
Sheets("Details List").Range("K" & nr + 1) = bCell.Offset(0, 2)
End If

Next bCell

Sheets("Details List").Columns("K:K").AutoFit

End Sub


Reply ↓  Report •

#8
June 28, 2017 at 05:37:44
Just found a bug :( had to rush this, this morning

Updated code. ....


Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    MaxValue = Application.Max(Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp).Row, Sheets("Raw Data").Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & MaxValue))
        
        If bCell <> Empty Then

            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell
            Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell
            End If
            
        Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell.Offset(0, 2)
        End If
    
    Next bCell
    
    Sheets("Detail List").Columns("K:K").AutoFit

End Sub

message edited by AlwaysWillingToLearn


Reply ↓  Report •

#9
June 28, 2017 at 17:49:10
thanks for your help! this code works but it starts from K2 cell instead of K3. and if I run it 2 times it will continue to add the same combination in column K... so the K column will continue to grow.... like now below:-

	     K
1	(blank)
2	vendor code1
3	customer code1
4	customer code2
5	customer code3
6	vendor code2
7	vendor code3
8	customer code4
9	customer code5
10	customer code6
11	vendor code4
12	vendor code1
13	customer code1
14	customer code2
15	customer code3
16	vendor code2
17	vendor code3
18	customer code4
19	customer code5
20	customer code6
21	vendor code4

message edited by suzysss


Reply ↓  Report •

#10
June 29, 2017 at 00:05:17
I don't understand why it will start from row 2 as the code specifically says row 3. Anyways I have added some code to clear column K before inserting the new values so that the list does not keep growing.

Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    Sheets("Detail List").Columns("K:K").ClearContents
    
    MaxValue = Application.Max(Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp).Row, Sheets("Raw Data").Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & MaxValue))
        
        If bCell <> Empty Then

            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell
            Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell
            End If
            
        Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell.Offset(0, 2)
        End If
    
    Next bCell
    
    Sheets("Detail List").Columns("K:K").AutoFit

End Sub


Reply ↓  Report •

#11
June 29, 2017 at 00:55:29
Thanks for your help. Yes it is not growing anymore!! I didn't know this VBA is so hard .... I thought it's a simple one like how we write excel formula
 'Detail list'!K3=IF('Raw data'!AS2="",'Raw data'!AU2,'Raw data'!AS2)
..... VBA is not for me.. I just try to use it to design one of my accounting report.. it is really hard.... T^T

As for the row 2, I found it actually depends.. if AS has value first, then it will start from K3, if AU has value first, then it will start from K2....

so if Raw data sheet is like this

	   AS	        AU
1	(blank*)	(blank*)
2	(blank*)	vendor code1
3	customer code1	(blank*)
4	customer code2	(blank*)
5	customer code3	(blank*)
6	(blank*)	vendor code 2
7	(blank*)	vendor code 3
8	customer code4	(blank*)
9	customer code 5	(blank*)
10	customer code6	(blank*)
11	(blank*)	vendor code 4

then run the code the Detail list sheet will be

	   K
1	(blank*)
2	vendor code1
3	customer code1
4	customer code2
5	customer code3
6	vendor code 2
7	vendor code 3
8	customer code4
9	customer code 5
10	customer code6
11	vendor code 4

but, if raw data sheet is like this

	    AS	         AU
1	(blank*)	(blank*)
2	customer code1	(blank*)
3	(blank*)	vendor code1
4	customer code2	(blank*)
5	customer code3	(blank*)
6	(blank*)	vendor code 2
7	(blank*)	vendor code 3
8	customer code4	(blank*)
9	customer code 5	(blank*)
10	customer code6	(blank*)
11	(blank*)	vendor code 4

then Detail sheet will be:

	   K
1	(blank*)
2	(blank*)
3	customer code1
4	vendor code1
5	customer code2
6	customer code3
7	vendor code 2
8	vendor code 3
9	customer code4
10	customer code 5
11	customer code6
12	vendor code 4


Anyway don't worry about it... i will try to do this column manually perhaps..


Reply ↓  Report •

#12
June 29, 2017 at 01:05:12
Good testing! I have fixed that issue

Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    Sheets("Detail List").Columns("K:K").ClearContents
    
    MaxValue = Application.Max(Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp).Row, Sheets("Raw Data").Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & MaxValue))
        
        If bCell <> vbNullString Then

            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell
            Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell
            End If
            
        Else
            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell.Offset(0, 2)
            Else
            
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell.Offset(0, 2)
            End If
        End If
    
    Next bCell
    
    Sheets("Detail List").Columns("K:K").AutoFit

End Sub


Reply ↓  Report •

#13
June 29, 2017 at 01:19:12
OMG!! real genius!! It is perfect!! Thanks alottttttt!!! ^^

Reply ↓  Report •

#14
June 29, 2017 at 01:23:06
No worries glad it worked :)

Reply ↓  Report •

#15
July 25, 2017 at 01:48:11
hello Alwayswillingtolearn.... after I use this code for 1 month.. I noted that the code will ignore those rows with both "AS" and "AU" blank....
meaning if the data is as below:-
	    AS	           AU
1	(blank*)	(blank*)
2	(blank*)	vendor code1
3	customer code1	(blank*)
4	customer code2	(blank*)
5	(blank*)	(blank*)
6	(blank*)	vendor code2
7	(blank*)	vendor code3
8	(blank*)	(blank*)
9	customer code3	(blank*)
10	customer code4	(blank*)

then the result will be:

	   K
1	(blank*)
2	vendor code1
3	customer code1
4	customer code2
5	vendor code2
6	vendor code3
7	customer code3
8	customer code4

however the result I want is:

	   K
1	(blank*)
2	vendor code1
3	customer code1
4	customer code2
5	(blank*)
6	vendor code2
7	vendor code3
8	(blank*)
9	customer code3
10	customer code4

is there anyway to fix it? thanks so much!


Reply ↓  Report •

#16
August 2, 2017 at 00:33:12
Try this,

Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    Sheets("Detail List").Columns("K:K").ClearContents
    
    MaxValue = Application.Max(Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp).Row, Sheets("Raw Data").Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & MaxValue))
        
'        If bCell <> vbNullString Then

    If bCell = vbNullString And bCell.Offset(0, 2) = vbNullString Then bCell = "*"

            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell
            Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell
            End If
            
    '    Else
            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell.Offset(0, 2)
            Else
            
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell.Offset(0, 2)
            End If
'        End If
    
    Next bCell
    
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp))
        If bCell = "*" Then
            bCell = vbNullString
        End If
    Next bCell
    
    For Each bCell In Sheets("Detail List").Range("K1", Sheets("Detail List").Range("K" & Rows.Count).End(xlUp))
        If bCell = "*" Then
            bCell = vbNullString
        End If
    Next bCell
    
    
    Sheets("Detail List").Columns("K:K").AutoFit

End Sub


Reply ↓  Report •

#17
August 2, 2017 at 02:04:39
thanks, it is faster than previous code, and it's almost there!

just can the code to copy from row 2 of raw data sheet to row 3 of detail list sheet?

right now what it does is:-

from

	    AS	          AU
1	AS raw 1	AU raw 1
2	(blank*)	vendor code 1
3	customer code 1	(blank*)
4	customer code 2	(blank*)
5	(blank*)	(blank*)
6	(blank*)	vendor code 2
7	(blank*)	vendor code 3
8	(blank*)	(blank*)
9	customer code 3	(blank*)
10	customer code 4	(blank*)

to

	   K
1	(blank*)
2	(blank*)
3	AS raw 1
4	AU raw 1
5	vendor code 1
6	customer code 1
7	customer code 2
8	(blank*)
9	vendor code 2
10	vendor code 3
11	(blank*)
12	customer code 3
13	customer code 4

but can I don't copy row 1 of raw data sheet and make the result like:-

	   K
1	(blank*)
2	(blank*)
3	vendor code 1
4	customer code 1
5	customer code 2
6	(blank*)
7	vendor code 2
8	vendor code 3
9	(blank*)
10	customer code 3
11	customer code 4


Reply ↓  Report •

#18
August 2, 2017 at 06:26:10
Ok, I think I understand what you mean, try this

Sub CopyToDL()

    Dim iString As String
    Dim bCell As Range
    Dim MaxValue As Long
        
    iString = vbNullString
    
    Sheets("Detail List").Columns("K:K").ClearContents
    
    MaxValue = Application.Max(Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp).Row, Sheets("Raw Data").Range("AU" & Rows.Count).End(xlUp).Row)
        
    For Each bCell In Sheets("Raw Data").Range("AS2", Sheets("Raw Data").Range("AS" & MaxValue))
        
'        If bCell <> vbNullString Then

    If bCell = vbNullString And bCell.Offset(0, 2) = vbNullString Then bCell = "*"

            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell
            Else
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell
            End If
            
    '    Else
            If Sheets("Detail List").Range("K3") = vbNullString Then
                Sheets("Detail List").Range("K3") = bCell.Offset(0, 2)
            Else
            
                nr = Sheets("Detail List").Range("K" & Rows.Count).End(xlUp).Row
                Sheets("Detail List").Range("K" & nr + 1) = bCell.Offset(0, 2)
            End If
'        End If
    
    Next bCell
    
    For Each bCell In Sheets("Raw Data").Range("AS1", Sheets("Raw Data").Range("AS" & Rows.Count).End(xlUp))
        If bCell = "*" Then
            bCell = vbNullString
        End If
    Next bCell
    
    For Each bCell In Sheets("Detail List").Range("K1", Sheets("Detail List").Range("K" & Rows.Count).End(xlUp))
        If bCell = "*" Then
            bCell = vbNullString
        End If
    Next bCell
    
    
    Sheets("Detail List").Columns("K:K").AutoFit

End Sub


Reply ↓  Report •

#19
August 2, 2017 at 23:07:25
you are really an expert in VBA, thanks so much!

yes it works, just for some reason it erases whatever I have in K1 and K2 cells.... and if I have 10,000 rows, it took about 5 mins to run the code...

anyway this is small thing, I can still use it.. thanks a lot!


Reply ↓  Report •

Ask Question