PDA

View Full Version : Create if statement: if value exists then create new sheet



sharc316
03-14-2017, 06:14 AM
Hi,

I have a piece of code below that looks for values, "Tractor Reparis/Misc" and "Total Authorized Chargebacks" and then copies all data between those values and pastes it into a new sheet called "Repairs and Chargebacks".

What I would like this code to do is to only create the sheet "Repairs and Chargebacks" if the values "Tractor Reparis/Misc" and "Total Authorized Chargebacks" exist within the sheet. If they do not then do nothing and move on.

Please help.



For rownum = 1 To lastrow
Do
If .Cells(rownum, 1).Value = "TRACTOR REPAIRS/MISC" Then
startrow = rownum
End If
rownum = rownum + 1
If (rownum > lastrow) Then Exit For
Loop Until .Cells(rownum, 1).Value = "TOTAL AUTHORIZED CHARGEBACKS"
endrow = rownum
rownum = rownum + 1
Worksheets("sheet1").Range(startrow & ":" & endrow).Copy
Sheets.Add
ActiveSheet.Name = ("Repairs and Chargebacks")
Sheets("Repairs and Chargebacks").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)

mdmackillop
03-14-2017, 06:56 AM
Sub Test()
Dim c As Range, d As Range
Dim sh As Worksheet

With Columns(1)
Set c = .Find("TRACTOR REPAIRS/MISC")
Set d = .Find("TOTAL AUTHORIZED CHARGEBACKS")
End With


If Not c Is Nothing Then
Set sh = Sheets.Add(after:=Sheets(Sheets.Count))
sh.Name = "Repairs and Chargebacks"
Range(c, d).EntireRow.Copy sh.Cells(1, 1)
Else
MsgBox "TRACTOR REPAIRS/MISC not found"
End If
End Sub

sharc316
03-14-2017, 07:55 AM
Thank you mdmackillop. Can you please let me know if the code you provided replaces the code I have pasted above? If not, where should it go? Sorry, new at this.

mdmackillop
03-14-2017, 01:11 PM
We all started from there.
Here's a sample workbook

sharc316
03-14-2017, 06:01 PM
Thank you for providing this, but cant see to make it work in my code. I did not want to crowd the post with my entire code but maybe it will provide a better view on why it's not working. To reitterate, the code below looks for values within the sheet and then copies all data between those values and pastes it into a new sheet.

In some instances some of the values below do not show up since there is no data in certain periods for them. For example "Tractor Reparis/Misc" and "Total Authorized Chargebacks" were not in one of the statements. So in this case the code below would copy data from the previous range which is "Fuel Purchase" and "Fuel Purchase Total" and pastes into the chargebacks section, which is not correct. I woud like to avoid creating worksheets if there is not data available to display.
Hope this makes it a bit clearer. Please help, and thank you for your time.


Sub dataextract()
Dim rownum As Long
Dim colnum As Long
Dim startrow As Long
Dim endrow As Long
Dim lastrow As Long
rownum = 1
colnum = 1
lastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("sheet1").Range("a1:a" & lastrow)
'Add End Data to last row to create range
ActiveSheet.Cells(lastrow, "A").Value = "END DATA"
'Linehaul Trips Section
For rownum = 1 To lastrow
Do
If .Cells(rownum, 1).Value = "LINEHAUL TRIPS" Then
startrow = rownum
End If
rownum = rownum + 1
If (rownum > lastrow) Then Exit For
Loop Until .Cells(rownum, 1).Value = "GROSS LINEHAUL ACTIVITY:"
endrow = rownum
rownum = rownum + 1
Worksheets("sheet1").Range(startrow & ":" & endrow).Copy
Sheets.Add
ActiveSheet.Name = ("Linehaul Trips")
Sheets("Linehaul Trips").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count)
'Inserting column B in Linehaul Trips tab
Range("B1").EntireColumn.Insert
'Add column B heading name: Vehicle #
[B2].Value = "VEHICLE #"
'Add vehicle number into each row
Dim vehicleRng As Range, cell As Range
With Range("A2", Cells(Rows.Count, 1).End(xlUp))
.AutoFilter field:=1, Criteria1:="VEHICLE"
Set vehicleRng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
End With
ActiveSheet.AutoFilterMode = False
For Each cell In vehicleRng
With cell
Range(cell.Offset(1), cell.End(xlDown).Offset(-1)).Offset(, 1).Value = cell.Offset(, 2)
End With
Next
'End vehicle number code
Next rownum
End With
lastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("sheet1").Range("a1:a" & lastrow)
'Fuel Purchases Section
For rownum = 1 To lastrow
Do
If .Cells(rownum, 1).Value = "FUEL PURCHASES" Then
startrow = rownum
End If
rownum = rownum + 1
If (rownum > lastrow) Then Exit For
Loop Until .Cells(rownum, 1).Value = "FUEL PURCHASE TOTAL"
endrow = rownum
rownum = rownum + 1
Worksheets("sheet1").Range(startrow & ":" & endrow).Copy
Sheets.Add
ActiveSheet.Name = ("Fuel Purchases")
Sheets("Fuel Cost").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count)
'Convert text values to number on Fuel Cost tab
Range("A:Z").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Next rownum
End With
lastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("sheet1").Range("a1:a" & lastrow)
'Repairs and Chargebacks Section
For rownum = 1 To lastrow
Do
If .Cells(rownum, 1).Value = "TRACTOR REPAIRS/MISC" Then
startrow = rownum
End If
rownum = rownum + 1
If (rownum > lastrow) Then Exit For
Loop Until .Cells(rownum, 1).Value = "TOTAL AUTHORIZED CHARGEBACKS"
endrow = rownum
rownum = rownum + 1
Worksheets("sheet1").Range(startrow & ":" & endrow).Copy
Sheets.Add
ActiveSheet.Name = ("Repairs and Chargebacks")
Sheets("Repairs and Chargebacks").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count)
'Convert text values to number on Repairs and Chargebacks tab
Range("A:Z").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Next rownum
End With
lastrow = Worksheets("sheet1").Range("A65536").End(xlUp).Row
With ActiveWorkbook.Worksheets("sheet1").Range("a1:a" & lastrow)
'Additional Info Section
For rownum = 1 To lastrow
Do
If .Cells(rownum, 1).Value = "ADDITIONAL INFORMATION:" Then
startrow = rownum
End If
rownum = rownum + 1
If (rownum > lastrow) Then Exit For
Loop Until .Cells(rownum, 1).Value = "END DATA"
endrow = rownum
rownum = rownum + 1
Worksheets("sheet1").Range(startrow & ":" & endrow).Copy
Sheets.Add
ActiveSheet.Name = ("Additional Info")
Sheets("Additional Info").Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Move after:=Sheets(ActiveWorkbook.Sheets.Count)
'Convert text values to number on Additional Information tab
Range("A:Z").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Next rownum
End With
End Sub

mdmackillop
03-15-2017, 02:53 AM
Can you Post a workbook (http://www.vbaexpress.com/forum/faq.php?s=&do=search&q=attachment&titleandtext=1&match=all) with your code and sanitized data?

sharc316
03-18-2017, 10:21 AM
Hi - I've added two sample data files below. The code is in sheet 1 for each. It's the same code to format the data. Basically search two values and pull the data between those two values in a different tab.
Here is the issue:

In file "KRawData" all values exist so everything is pulled correctly

In file "BRawData" there is no information for "Tractor Reparis/Misc" and "Total Authorized Chargebacks" and therefore these two values do not show up in the statements. The code still creates the "Repairs and Chargebacks" tab for them and then pastes the data from the previous tab ("Fuel Purchases") in it.

I would like to not have any data posted if the values do not exist in the statement.

Please let me know if there any questions and thank you for your help.