Adding IF statement to this VBA code?

C

Coyotex3

Active Member
Joined
Dec 12, 2021
Messages
496
Office Version
  1. 365
Platform
  1. Windows
This is the code I used which copies the information from sheets("Customer Info") into "Sheet1"

VBA Code: 
Sub CopyOrder()
  Dim sh1 As Worksheet, sh3 As Worksheet
  Dim i As Long
 
  Set sh1 = Sheets("Customer Info")
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
    Set sh3 = ActiveSheet
    sh3.Name = "Order" & i - 5
    sh3.Range("B4").Value = Date
    sh3.Range("B6").Value = Date + 1
    sh3.Range("B8").Value = "No"
    sh3.Range("B11").Value = sh1.Range("R" & i).Value
    sh3.Range("B19").Value = sh1.Range("B" & i).Value
    sh3.Range("B14").Value = sh1.Range("D" & i).Value
    sh3.Range("B15").Value = sh1.Range("E" & i).Value
    sh3.Range("B16").Value = sh1.Range("F" & i).Value
    sh3.Range("B20").Value = sh1.Range("H" & i).Value
    sh3.Range("B34").Value = "Jane Doe"
    sh3.Range("B38").Value = "John Doe"
    sh3.Range("E14").Value = sh1.Range("O" & i).Value
    sh3.Range("E15").Value = sh1.Range("P" & i).Value
    sh3.Range("E16").Value = sh1.Range("Q" & i).Value
  
  Next
End Sub

Some of the customers on the sheet "Customer Information" will have additional orders. How could I make an additional macro which will copy information from "Customer Info" Into "sheet2" IF column H is not blank?

VBA Code: 
Range("H:H").Value<>""

Something like that perhaps?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
I was able to get this code to do what I want. How could I tell it to stop once column H is empty? Right now it is making blank copies.

VBA Code: 
Sub CopyOrder()
  Dim sh1 As Worksheet, sh3 As Worksheet
  Dim i As Long
  
  Set sh1 = Sheets("Customer Info")
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
    Set sh3 = ActiveSheet
    sh3.Name = "Order" & i - 5
    If sh1.Range("H" & i).Value <> "" Then
    sh3.Range("B4").Value = Date
    sh3.Range("B6").Value = Date + 1
    sh3.Range("B8").Value = "No"
    sh3.Range("B11").Value = sh1.Range("R" & i).Value
    sh3.Range("B19").Value = sh1.Range("B" & i).Value
    sh3.Range("B14").Value = sh1.Range("D" & i).Value
    sh3.Range("B15").Value = sh1.Range("E" & i).Value
    sh3.Range("B16").Value = sh1.Range("F" & i).Value
    sh3.Range("B20").Value = sh1.Range("H" & i).Value
    sh3.Range("B34").Value = "Jane Doe"
    sh3.Range("B38").Value = "John Doe"
    sh3.Range("E14").Value = sh1.Range("O" & i).Value
    sh3.Range("E15").Value = sh1.Range("P" & i).Value
    sh3.Range("E16").Value = sh1.Range("Q" & i).Value
   End If
  Next
End Sub
 
Upvote 0
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your "Customer Info" sheet and "Sheet2" sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
mumps said:
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not a pictures) of your "Customer Info" sheet and "Sheet2" sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Click to expand...
Thank you Mumps. I would try to get it over to an XL2BB sheet, but it is a lot of sensitive information and it would take me a while to make mock templates.

The code that I have, does what I need it to do. It copies the information from the row in Sheet "Customer Info" into "Sheet2" if Column H is not blank.

I would like for it to not make a copy of "sheet2"if Column H IS BLANK. Right now it is giving me blank copies of "Sheet2" when column H is blank.
 
Upvote 0
Try:
VBA Code: 
Sub CopyOrder()
    Dim sh1 As Worksheet, i As Long
    Set sh1 = Sheets("Customer Info")
    For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
        If sh1.Range("H" & i).Value <> "" Then
             Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
             With ActiveSheet
                .Name = "Order" & i - 5
                .Range("B4").Value = Date
                .Range("B6").Value = Date + 1
                .Range("B8").Value = "No"
                .Range("B11").Value = sh1.Range("R" & i).Value
                .Range("B19").Value = sh1.Range("B" & i).Value
                .Range("B14").Value = sh1.Range("D" & i).Value
                .Range("B15").Value = sh1.Range("E" & i).Value
                .Range("B16").Value = sh1.Range("F" & i).Value
                .Range("B20").Value = sh1.Range("H" & i).Value
                .Range("B34").Value = "Jane Doe"
                .Range("B38").Value = "John Doe"
                .Range("E14").Value = sh1.Range("O" & i).Value
                .Range("E15").Value = sh1.Range("P" & i).Value
                .Range("E16").Value = sh1.Range("Q" & i).Value
            End With
        End If
    Next i
End Sub
I've tidied up your code a little bit.
 
Upvote 0
mumps said:
Try:
VBA Code: 
Sub CopyOrder()
    Dim sh1 As Worksheet, i As Long
    Set sh1 = Sheets("Customer Info")
    For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
        If sh1.Range("H" & i).Value <> "" Then
             Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
             With ActiveSheet
                .Name = "Order" & i - 5
                .Range("B4").Value = Date
                .Range("B6").Value = Date + 1
                .Range("B8").Value = "No"
                .Range("B11").Value = sh1.Range("R" & i).Value
                .Range("B19").Value = sh1.Range("B" & i).Value
                .Range("B14").Value = sh1.Range("D" & i).Value
                .Range("B15").Value = sh1.Range("E" & i).Value
                .Range("B16").Value = sh1.Range("F" & i).Value
                .Range("B20").Value = sh1.Range("H" & i).Value
                .Range("B34").Value = "Jane Doe"
                .Range("B38").Value = "John Doe"
                .Range("E14").Value = sh1.Range("O" & i).Value
                .Range("E15").Value = sh1.Range("P" & i).Value
                .Range("E16").Value = sh1.Range("Q" & i).Value
            End With
        End If
    Next i
End Sub
I've tidied up your code a little bit.
Click to expand...
Thank you, Mumps. Someone helped me out and pretty much built this amazing code for me. I just altered it a bit.

This code makes blank sheets when row H is blank. Any way of making it do nothing once column H is blank?
 
Upvote 0
The macro I suggested should not be creating blank sheets if any cell in column H is truly blank. If it is creating blank sheets, please follow the steps I outlined in Post #3 to post sample data or to upload your file. You can replace any confidential information with generic data. I don't need all the data. A dozen or so rows would probably be enough.
 
Upvote 0
Order Template.xlsx
ABCDEFGHIJKLMNOP
1CityOrder RecipientPrimary AddressSecondary AddressCity, State, Zip CodePrimary OrderSecondary OrderThird OrderAdditional OrderAmount(1)Amount(2)Amount(3)BirthdayAccount Type
2New YorkJohn Doe 1123 Make it Happen150 We Did ItManhattan, NY, 1000112545545687585463210100754610001/10/1950Premier
3New YorkJohn Doe 2124 Make it Happen151 We Did ItManhattan, NY, 10002125465456975854632111000754710001/10/1950Preferred
4New YorkJohn Doe 3125 Make it Happen152 We Did ItManhattan, NY, 1000312547545707585463212150754810001/10/1950Preferred
5New YorkJohn Doe 4126 Make it HappenManhattan, NY, 10004125485457175854632132000754910001/10/1950Preferred
6New YorkJohn Doe 5127 Make it HappenManhattan, NY, 100051254954572758545000755010001/10/1950Preferred
7New YorkJohn Doe 6128 Make it HappenManhattan, NY, 100061255054573758545000755110001/10/1950Preferred
8New YorkJohn Doe 7129 Make it HappenManhattan, NY, 10007125515457475854546556000755210001/10/1950Preferred
9New YorkJohn Doe 8130 Make it HappenManhattan, NY, 100081255254575758546465467000755310001/10/1950Preferred
10New YorkJohn Doe 9131 Make it HappenManhattan, NY, 10009125535457675854654657000755410001/10/1950Preferred
11
12
13
Sheet1



Order Template.xlsx
ABCDEFG
1Order Form
2TODAY'S DATE:1/10/2021
3DATE NEEDED:1/11/2021
4Order Type
5Point of Contact:John Doe 1
6Total Due1,000
7Order Recipient:
8Primary Address123 Make it Happen
9Secondary Address150 We Did It
10City, State, Zip CodeManhattan, NY, 10001
11Additional Information
12Seller:
13Primary Order12545Amount(1)$100.00
14Secondary Order54568Amount(2)$7,546.00
15Third Order75854Amount(3)$1,000.00
16Additional Order63210Amount(4)
17Description:
18Signature:
19Manager Print:
20Signature:
21Date:
22
23Reason
24
25
26
27
28
29
30
31
32
33
34
Sheet2


Not exactly the same format, but similar. Lets say I wanted to run the macro from earlier on a similar format but this time based off of Column "I" (due to this format). I would like for this macro to only make 7 copies since Column I is empty on rows 6 and 7. The macro that I have and the one you helped me out with, will copy the information into the new row whether column I is blank or not( Column H in our previous case)


But how can I get it to NOT copy information if the column I is empty on the row in question?

Hope this makes a bit more sense.
 
Upvote 0
The macro worked for me. I modified it a bit to make the added sheet names sequential.
Rich (BB code): 
Sub CopyOrder()
    Dim sh1 As Worksheet, i As Long, x As Long: x = 2
    Set sh1 = Sheets("Customer Info")
    For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
        If sh1.Range("I" & i).Value <> "" Then
             Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
             With ActiveSheet
                .Name = "Order" & x - 5
                x = x + 1
                .Range("B4").Value = Date
                .Range("B6").Value = Date + 1
                .Range("B8").Value = "No"
                .Range("B11").Value = sh1.Range("R" & i).Value
                .Range("B19").Value = sh1.Range("B" & i).Value
                .Range("B14").Value = sh1.Range("D" & i).Value
                .Range("B15").Value = sh1.Range("E" & i).Value
                .Range("B16").Value = sh1.Range("F" & i).Value
                .Range("B20").Value = sh1.Range("H" & i).Value
                .Range("B34").Value = "Jane Doe"
                .Range("B38").Value = "John Doe"
                .Range("E14").Value = sh1.Range("O" & i).Value
                .Range("E15").Value = sh1.Range("P" & i).Value
                .Range("E16").Value = sh1.Range("Q" & i).Value
            End With
        End If
    Next i
End Sub
The code between the "If...End If" section will run only if the line of code in red is true. Since I6:I7 are empty, Sheet2 was not copied.
 
Upvote 0
Solution
mumps said:
The macro worked for me. I modified it a bit to make the added sheet names sequential.
Rich (BB code): 
Sub CopyOrder()
    Dim sh1 As Worksheet, i As Long, x As Long: x = 2
    Set sh1 = Sheets("Customer Info")
    For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
        If sh1.Range("I" & i).Value <> "" Then
             Sheets("Sheet2").Copy after:=Sheets(Sheets.Count)
             With ActiveSheet
                .Name = "Order" & x - 5
                x = x + 1
                .Range("B4").Value = Date
                .Range("B6").Value = Date + 1
                .Range("B8").Value = "No"
                .Range("B11").Value = sh1.Range("R" & i).Value
                .Range("B19").Value = sh1.Range("B" & i).Value
                .Range("B14").Value = sh1.Range("D" & i).Value
                .Range("B15").Value = sh1.Range("E" & i).Value
                .Range("B16").Value = sh1.Range("F" & i).Value
                .Range("B20").Value = sh1.Range("H" & i).Value
                .Range("B34").Value = "Jane Doe"
                .Range("B38").Value = "John Doe"
                .Range("E14").Value = sh1.Range("O" & i).Value
                .Range("E15").Value = sh1.Range("P" & i).Value
                .Range("E16").Value = sh1.Range("Q" & i).Value
            End With
        End If
    Next i
End Sub
The code between the "If...End If" section will run only if the line of code in red is true. Since I6:I7 are empty, Sheet2 was not copied.
Click to expand...
Mumps I concede that it is absolutely working today. THANK YOU SO MUCH!
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Data not copying fully into ListBox2
Replies
0
Views
138
Patriot2879
P
B
  • Question
Transferring data utilising VBA
2
Replies
18
Views
1K
sijpie
sijpie
S
  • Question
Copy a column value when the values match to multiple sheets of the workbook
Replies
1
Views
244
sofas
S
E
  • Solved
Copy & Paste two ranges from one sheet and merge to a single range on another sheet.
2 3
Replies
22
Views
1K
Edgarvelez
E
J
  • Solved
VBA to Copy Value of Cell in a Do Until Loop
Replies
2
Views
698
JarrydBarnard
J

Forum statistics

Threads
1,215,459
Messages
6,124,947
Members
449,198
Latest member
MhammadishaqKhan

Share this page

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