clean up code a little
in [General]
|
From: terilad on 7 Apr 2010 10:47 Hi I have a copy and paste code I was wondering if there is a way to clean it up a little to run it more better. Here's my code. Sub ApplyStock() Range("C3").Copy Sheet4.Select Range("B7").Select ActiveSheet.Paste Sheets("Stock Maintenance").Select Range("C4").Copy Sheet5.Select Range("B7").Select ActiveSheet.Paste Sheets("Stock Maintenance").Select Range("C5").Copy Sheet6.Select Range("B7").Select ActiveSheet.Paste Sheets("Stock Maintenance").Select Range("C6").Copy Sheet7.Select Range("B7").Select ActiveSheet.Paste End Sub Many thanks Mark
From: JLatham on 7 Apr 2010 11:07 I think all of that boils down to: Sub ApplyStock() Sheet4.range("B7") = ActiveSheet.range("C3") Sheet5.Range("B7") = Sheets("Stock Maintenance").range("C4") Sheet6.Range("B7") = Sheets("Stock Maintenance").Range("C5") Sheet7.Range("B7") =Sheets("Stock Maintenance").Range("C6") End Sub Problem is I'm not sure what sheet you are starting out on, and that first statement is dependent on the current/active selected sheet. You could make sure of the starting sheet by adding a statement similar to: Sheets("Stock Maintenance").Select as the first line in the whole command sequence, changing the sheet name as required. "terilad" wrote: > Hi I have a copy and paste code > > I was wondering if there is a way to clean it up a little to run it more > better. > > Here's my code. > > Sub ApplyStock() > Range("C3").Copy > Sheet4.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C4").Copy > Sheet5.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C5").Copy > Sheet6.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C6").Copy > Sheet7.Select > Range("B7").Select > ActiveSheet.Paste > End Sub > > Many thanks > > > Mark
From: terilad on 7 Apr 2010 11:12 If I change the code to the one you suggested when I clear the cell data in stock maintenance this will remove the data from the cell the value is being put into? Mark "JLatham" wrote: > I think all of that boils down to: > Sub ApplyStock() > Sheet4.range("B7") = ActiveSheet.range("C3") > Sheet5.Range("B7") = Sheets("Stock Maintenance").range("C4") > Sheet6.Range("B7") = Sheets("Stock Maintenance").Range("C5") > Sheet7.Range("B7") =Sheets("Stock Maintenance").Range("C6") > End Sub > > Problem is I'm not sure what sheet you are starting out on, and that first > statement is dependent on the current/active selected sheet. You could make > sure of the starting sheet by adding a statement similar to: > Sheets("Stock Maintenance").Select > as the first line in the whole command sequence, changing the sheet name as > required. > > "terilad" wrote: > > > Hi I have a copy and paste code > > > > I was wondering if there is a way to clean it up a little to run it more > > better. > > > > Here's my code. > > > > Sub ApplyStock() > > Range("C3").Copy > > Sheet4.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C4").Copy > > Sheet5.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C5").Copy > > Sheet6.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C6").Copy > > Sheet7.Select > > Range("B7").Select > > ActiveSheet.Paste > > End Sub > > > > Many thanks > > > > > > Mark
From: Don Guillett on 7 Apr 2010 11:24 Sub ApplyStock() with Sheets("Stock Maintenance") ..Range("C3").Copy sheet4.Range("B7") ..Range("C4").Copy Sheet5.Range("B7") ..Range("C5").Copy Sheet6.Range("B7") ..Range("C6").Copy Sheet7.Range("B7") end with End Sub Even better Sub stockbetter() With Sheets("sheet1") For i = 3 To 6 .Cells(i, "c").Copy Sheets(i + 1).Range("b7") Next i End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "terilad" <terilad(a)discussions.microsoft.com> wrote in message news:1FA8951C-095E-4939-A7C5-D46004235B88(a)microsoft.com... > Hi I have a copy and paste code > > I was wondering if there is a way to clean it up a little to run it more > better. > > Here's my code. > > Sub ApplyStock() > Range("C3").Copy > Sheet4.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C4").Copy > Sheet5.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C5").Copy > Sheet6.Select > Range("B7").Select > ActiveSheet.Paste > Sheets("Stock Maintenance").Select > Range("C6").Copy > Sheet7.Select > Range("B7").Select > ActiveSheet.Paste > End Sub > > Many thanks > > > Mark
From: terilad on 7 Apr 2010 11:32
I am starting out on stock maintenance sheet Mark "JLatham" wrote: > I think all of that boils down to: > Sub ApplyStock() > Sheet4.range("B7") = ActiveSheet.range("C3") > Sheet5.Range("B7") = Sheets("Stock Maintenance").range("C4") > Sheet6.Range("B7") = Sheets("Stock Maintenance").Range("C5") > Sheet7.Range("B7") =Sheets("Stock Maintenance").Range("C6") > End Sub > > Problem is I'm not sure what sheet you are starting out on, and that first > statement is dependent on the current/active selected sheet. You could make > sure of the starting sheet by adding a statement similar to: > Sheets("Stock Maintenance").Select > as the first line in the whole command sequence, changing the sheet name as > required. > > "terilad" wrote: > > > Hi I have a copy and paste code > > > > I was wondering if there is a way to clean it up a little to run it more > > better. > > > > Here's my code. > > > > Sub ApplyStock() > > Range("C3").Copy > > Sheet4.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C4").Copy > > Sheet5.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C5").Copy > > Sheet6.Select > > Range("B7").Select > > ActiveSheet.Paste > > Sheets("Stock Maintenance").Select > > Range("C6").Copy > > Sheet7.Select > > Range("B7").Select > > ActiveSheet.Paste > > End Sub > > > > Many thanks > > > > > > Mark |