Shorten Code?

T

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
216
amoverton2
A
K
  • Question
Comparing two sheets
Replies
3
Views
858
Killragtshirts
K
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
325
sspatriots
S
J
  • Solved
VBA for adding formulas to specific Columns when a new row is added.
Replies
7
Views
221
dreid1011
dreid1011
J
  • Question
Finding duplicates in rows of dates against the leave codes
2
Replies
10
Views
863
jaysbp
J

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

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