Shorten Code?

tnemom

Board Regular
Joined
May 14, 2007
Messages
67
I have the following code that copies & pastes data...so far it is only written for one IF case. Eventually it will have many different cases and will be pages upon pages of code. Just wondering if there is any way of condensing the code so that it isn't as long? Sorry for the long post! :eek:

Code:
Sub CREATEESTIMATE()

Dim wsEst As Worksheet
Dim wsList As Worksheet
Dim wsData As Worksheet
Dim smallrng As Range
Dim destrng As Range


Set wsEst = Worksheets("Estimate")
Set wsData = Worksheets("Brk-oil-44kv-data")
Set wsList = Worksheets("Estimate List")
lngMax = wsList.Cells(Rows.Count, 1).End(xlUp).Row

For Each Cell In wsList.Range("F2:F" & lngMax)

  
    If Cell.Value = "BR-O-44-RR" Then
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B2:E7").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F2:F7").Value
        End With
            Application.CutCopyMode = False
            Cells(myrow + 6, 2) = "Task#" & mynum
            Application.DisplayAlerts = True
            
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B8:E13").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F8:F13").Value
            
        End With
            Application.CutCopyMode = False
            Cells(myrow + 6, 2) = "Task#" & mynum
            Application.DisplayAlerts = True
            
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B14:E19").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F14:F19").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B20:E25").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F20:F25").Value
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B26:F31").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F26:F31").Value
        
        End With
                   
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B32:E37").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F32:F37").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B38:E43").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F38:F43").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B44:E49").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F44:F49").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B50:E55").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F50:F55").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B56:E61").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F56:F61").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B62:E67").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F62:F67").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 5, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=lxDown
            newrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
            Range(Cells(newrow, 3), Cells(newrow + 5, 5)).Value = wsData.Range("B68:E73").Value
            Range(Cells(newrow, 12), Cells(newrow + 5, 12)).Value = wsData.Range("F68:F73").Value
        
        End With
           
    Application.CutCopyMode = False
    Cells(myrow + 6, 2) = "Task#" & mynum
    Application.DisplayAlerts = True
    
    
    Sheets("Estimate").Activate
    ActiveSheet.unprotect
    Application.DisplayAlerts = False
    myrow = Cells.Find("Total Central Maintenance Shops Estimate").Row - 12
    mycell = Cells(myrow, 2)
    mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
    
        With Range(Cells(myrow, 2), Cells(myrow + 11, 2))
            .EntireRow.Copy
            .EntireRow.Insert Shift:=xlDown
            newrow = Cells.Find("Total Central Maintenance Shops Estimate").Row - 12
            Range(Cells(newrow, 3), Cells(newrow + 11, 5)).Value = wsData.Range("B75:E86").Value
            Range(Cells(newrow, 8), Cells(newrow + 11, 9)).Value = wsData.Range("G75:H86").Value
            Range(Cells(newrow, 12), Cells(newrow + 11, 12)).Value = wsData.Range("J75:J86").Value
        End With
        
    Application.CutCopyMode = False
    Cells(myrow + 12, 2) = "Task#" & mynum
    Application.DisplayAlerts = True

End If

Next Cell
End Sub

Thanks
TNEMOM
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
What is the code actually meant to do?

And why do you keep on repeating this section of code?
Code:
  Sheets("Estimate").Activate 
    ActiveSheet.unprotect 
    Application.DisplayAlerts = False 
    myrow = Cells.Find("Total Station Electrical / Cable / Mechanical / Civil Maintenance Estimate").Row - 6
 

tnemom

Board Regular
Joined
May 14, 2007
Messages
67
The code copies raw data from one sheet and enters it into an Estimate Sheet that creates costs of particular jobs. The users have a series of generic estimates that they work from, and they wanted me to create something where they only have to input some information and based upon that the estimate gets generated for them.

I kept repeating that part because I thought I had to. I'm relatively new to writing VBA and I just kept copying and pasting the section of code.

Thanks,
Tnemom
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,334
Messages
5,769,473
Members
425,553
Latest member
chrisc297

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
Top