clean up code a little [General]

Prev: Index/Match vs VLookup?
Next: Lookup for Part of the number

From: terilad on 7 Apr 2010 13:24

Don,

How could this coe be wriiten for the cells to copy from are from stock
maintenance sheet and cells c3,c4,c5 and so until c52 and then cells f3,f4,f5
until f52

Any ideas.

Thanks

Mark

"Don Guillett" wrote:

> 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: Don Guillett on 7 Apr 2010 14:00

From: JLatham on 7 Apr 2010 14:31

The cell on the right side of the = symbol will take on whatever value is in
the cell on the left side of the equation. They will be independent of one
another. That is, AFTER you have gone through this code, you can clear out
the data on the Stock Maintenance sheet and it will not affect the data on
the other sheets.

"terilad" wrote:

> 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: terilad on 12 Apr 2010 10:26

Hi Don,

I have rewritten code to be like your example but I get an error because of
another piece of code I have in the workbook, here is the code I am using
now:

Sub ApplyStock()
Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String
strPrompt = "WARNING!! This action will reset all stock cards and apply
new stock numbers" & vbNewLine & "" & vbNewLine & "The screen will flicker
for a period" & vbNewLine & "" & vbNewLine & "This action cannot be undone
are you sure you want to continue?"
intbuttons = vbYesNo + vbInformation
strTitle = "Stock Maintenance © M Neil "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
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")
Range("C7").Copy Sheet8.Range("B7")
Range("C8").Copy Sheet9.Range("B7")
Range("C9").Copy Sheet10.Range("B7")
Range("C10").Copy Sheet11.Range("B7")
Range("C11").Copy Sheet12.Range("B7")
Range("C12").Copy Sheet13.Range("B7")
Range("C13").Copy Sheet14.Range("B7")
Range("C14").Copy Sheet15.Range("B7")
Range("C15").Copy Sheet16.Range("B7")
Range("C16").Copy Sheet17.Range("B7")
Range("C17").Copy Sheet18.Range("B7")
Range("C18").Copy Sheet19.Range("B7")
Range("C19").Copy Sheet20.Range("B7")
Range("C20").Copy Sheet21.Range("B7")
Range("C21").Copy Sheet22.Range("B7")
Range("C22").Copy Sheet23.Range("B7")
Range("C23").Copy Sheet24.Range("B7")
Range("C24").Copy Sheet25.Range("B7")
Range("C25").Copy Sheet26.Range("B7")
Range("C26").Copy Sheet27.Range("B7")
Range("C27").Copy Sheet28.Range("B7")
Range("C28").Copy Sheet29.Range("B7")
Range("C29").Copy Sheet30.Range("B7")
Range("C30").Copy Sheet31.Range("B7")
Range("C31").Copy Sheet32.Range("B7")
Range("C32").Copy Sheet33.Range("B7")
Range("C33").Copy Sheet34.Range("B7")
Range("C34").Copy Sheet35.Range("B7")
Range("C35").Copy Sheet36.Range("B7")
Range("C36").Copy Sheet37.Range("B7")
Range("C37").Copy Sheet38.Range("B7")
Range("C38").Copy Sheet39.Range("B7")
Range("C39").Copy Sheet40.Range("B7")
Range("C40").Copy Sheet41.Range("B7")
Range("C41").Copy Sheet42.Range("B7")
Range("C42").Copy Sheet43.Range("B7")
Range("C43").Copy Sheet44.Range("B7")
Range("C44").Copy Sheet45.Range("B7")
Range("C45").Copy Sheet46.Range("B7")
Range("C46").Copy Sheet47.Range("B7")
Range("C47").Copy Sheet48.Range("B7")
Range("C48").Copy Sheet49.Range("B7")
Range("C49").Copy Sheet50.Range("B7")
Range("C50").Copy Sheet51.Range("B7")
Range("C51").Copy Sheet52.Range("B7")
Range("C52").Copy Sheet53.Range("B7")
Range("F3").Copy Sheet54.Range("B7")
Range("F4").Copy Sheet55.Range("B7")
Range("F5").Copy Sheet56.Range("B7")
Range("F6").Copy Sheet57.Range("B7")
Range("F7").Copy Sheet58.Range("B7")
Range("F8").Copy Sheet59.Range("B7")
Range("F9").Copy Sheet60.Range("B7")
Range("F10").Copy Sheet61.Range("B7")
Range("F11").Copy Sheet62.Range("B7")
Range("F12").Copy Sheet63.Range("B7")
Range("F13").Copy Sheet64.Range("B7")
Range("F14").Copy Sheet65.Range("B7")
Range("F15").Copy Sheet66.Range("B7")
Range("F16").Copy Sheet67.Range("B7")
Range("F17").Copy Sheet68.Range("B7")
Range("F18").Copy Sheet69.Range("B7")
Range("F19").Copy Sheet70.Range("B7")
Range("F20").Copy Sheet71.Range("B7")
Range("F21").Copy Sheet72.Range("B7")
Range("F22").Copy Sheet73.Range("B7")
Range("F23").Copy Sheet74.Range("B7")
Range("F24").Copy Sheet75.Range("B7")
Range("F25").Copy Sheet76.Range("B7")
Range("F26").Copy Sheet77.Range("B7")
Range("F27").Copy Sheet78.Range("B7")
Range("F28").Copy Sheet79.Range("B7")
Range("F29").Copy Sheet80.Range("B7")
Range("F30").Copy Sheet81.Range("B7")
Range("F31").Copy Sheet82.Range("B7")
Range("F32").Copy Sheet83.Range("B7")
Range("F33").Copy Sheet84.Range("B7")
Range("F34").Copy Sheet85.Range("B7")
Range("F35").Copy Sheet86.Range("B7")
Range("F36").Copy Sheet87.Range("B7")
Range("F37").Copy Sheet88.Range("B7")
Range("F38").Copy Sheet89.Range("B7")
Range("F39").Copy Sheet90.Range("B7")
Range("F40").Copy Sheet91.Range("B7")
Range("F41").Copy Sheet92.Range("B7")
Range("F42").Copy Sheet93.Range("B7")
Range("F43").Copy Sheet94.Range("B7")
Range("F44").Copy Sheet95.Range("B7")
Range("F45").Copy Sheet96.Range("B7")
Range("F46").Copy Sheet97.Range("B7")
Range("F47").Copy Sheet98.Range("B7")
Range("F48").Copy Sheet99.Range("B7")
Range("F49").Copy Sheet100.Range("B7")
Range("F50").Copy Sheet101.Range("B7")
Range("F51").Copy Sheet102.Range("B7")
Range("F52").Copy Sheet103.Range("B7")
Range("A1:A2").Select
End With
MsgBox "New stock numbers aplied"
End If
End Sub

Here is code I get an error with, is there a work around for this?

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Set t = Target
Set d = Range("D7:D36")
If Intersect(t, d) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Sheets(1).Activate
Range("A1").Select
End Sub

Many thanks for your help

Mark

"Don Guillett" wrote:

> 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: Don Guillett on 12 Apr 2010 12:14

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

And tell me where the error occurs
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"terilad" <terilad(a)discussions.microsoft.com> wrote in message
news:4D1738A7-41AE-4C58-8765-15977C0302E1(a)microsoft.com...
> Hi Don,
>
> I have rewritten code to be like your example but I get an error because
> of
> another piece of code I have in the workbook, here is the code I am using
> now:
>
> Sub ApplyStock()
> Dim strPrompt As String
> Dim intbuttons As Integer
> Dim strTitle As String
> strPrompt = "WARNING!! This action will reset all stock cards and apply
> new stock numbers" & vbNewLine & "" & vbNewLine & "The screen will flicker
> for a period" & vbNewLine & "" & vbNewLine & "This action cannot be undone
> are you sure you want to continue?"
> intbuttons = vbYesNo + vbInformation
> strTitle = "Stock Maintenance © M Neil "
> If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
> 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")
> Range("C7").Copy Sheet8.Range("B7")
> Range("C8").Copy Sheet9.Range("B7")
> Range("C9").Copy Sheet10.Range("B7")
> Range("C10").Copy Sheet11.Range("B7")
> Range("C11").Copy Sheet12.Range("B7")
> Range("C12").Copy Sheet13.Range("B7")
> Range("C13").Copy Sheet14.Range("B7")
> Range("C14").Copy Sheet15.Range("B7")
> Range("C15").Copy Sheet16.Range("B7")
> Range("C16").Copy Sheet17.Range("B7")
> Range("C17").Copy Sheet18.Range("B7")
> Range("C18").Copy Sheet19.Range("B7")
> Range("C19").Copy Sheet20.Range("B7")
> Range("C20").Copy Sheet21.Range("B7")
> Range("C21").Copy Sheet22.Range("B7")
> Range("C22").Copy Sheet23.Range("B7")
> Range("C23").Copy Sheet24.Range("B7")
> Range("C24").Copy Sheet25.Range("B7")
> Range("C25").Copy Sheet26.Range("B7")
> Range("C26").Copy Sheet27.Range("B7")
> Range("C27").Copy Sheet28.Range("B7")
> Range("C28").Copy Sheet29.Range("B7")
> Range("C29").Copy Sheet30.Range("B7")
> Range("C30").Copy Sheet31.Range("B7")
> Range("C31").Copy Sheet32.Range("B7")
> Range("C32").Copy Sheet33.Range("B7")
> Range("C33").Copy Sheet34.Range("B7")
> Range("C34").Copy Sheet35.Range("B7")
> Range("C35").Copy Sheet36.Range("B7")
> Range("C36").Copy Sheet37.Range("B7")
> Range("C37").Copy Sheet38.Range("B7")
> Range("C38").Copy Sheet39.Range("B7")
> Range("C39").Copy Sheet40.Range("B7")
> Range("C40").Copy Sheet41.Range("B7")
> Range("C41").Copy Sheet42.Range("B7")
> Range("C42").Copy Sheet43.Range("B7")
> Range("C43").Copy Sheet44.Range("B7")
> Range("C44").Copy Sheet45.Range("B7")
> Range("C45").Copy Sheet46.Range("B7")
> Range("C46").Copy Sheet47.Range("B7")
> Range("C47").Copy Sheet48.Range("B7")
> Range("C48").Copy Sheet49.Range("B7")
> Range("C49").Copy Sheet50.Range("B7")
> Range("C50").Copy Sheet51.Range("B7")
> Range("C51").Copy Sheet52.Range("B7")
> Range("C52").Copy Sheet53.Range("B7")
> Range("F3").Copy Sheet54.Range("B7")
> Range("F4").Copy Sheet55.Range("B7")
> Range("F5").Copy Sheet56.Range("B7")
> Range("F6").Copy Sheet57.Range("B7")
> Range("F7").Copy Sheet58.Range("B7")
> Range("F8").Copy Sheet59.Range("B7")
> Range("F9").Copy Sheet60.Range("B7")
> Range("F10").Copy Sheet61.Range("B7")
> Range("F11").Copy Sheet62.Range("B7")
> Range("F12").Copy Sheet63.Range("B7")
> Range("F13").Copy Sheet64.Range("B7")
> Range("F14").Copy Sheet65.Range("B7")
> Range("F15").Copy Sheet66.Range("B7")
> Range("F16").Copy Sheet67.Range("B7")
> Range("F17").Copy Sheet68.Range("B7")
> Range("F18").Copy Sheet69.Range("B7")
> Range("F19").Copy Sheet70.Range("B7")
> Range("F20").Copy Sheet71.Range("B7")
> Range("F21").Copy Sheet72.Range("B7")
> Range("F22").Copy Sheet73.Range("B7")
> Range("F23").Copy Sheet74.Range("B7")
> Range("F24").Copy Sheet75.Range("B7")
> Range("F25").Copy Sheet76.Range("B7")
> Range("F26").Copy Sheet77.Range("B7")
> Range("F27").Copy Sheet78.Range("B7")
> Range("F28").Copy Sheet79.Range("B7")
> Range("F29").Copy Sheet80.Range("B7")
> Range("F30").Copy Sheet81.Range("B7")
> Range("F31").Copy Sheet82.Range("B7")
> Range("F32").Copy Sheet83.Range("B7")
> Range("F33").Copy Sheet84.Range("B7")
> Range("F34").Copy Sheet85.Range("B7")
> Range("F35").Copy Sheet86.Range("B7")
> Range("F36").Copy Sheet87.Range("B7")
> Range("F37").Copy Sheet88.Range("B7")
> Range("F38").Copy Sheet89.Range("B7")
> Range("F39").Copy Sheet90.Range("B7")
> Range("F40").Copy Sheet91.Range("B7")
> Range("F41").Copy Sheet92.Range("B7")
> Range("F42").Copy Sheet93.Range("B7")
> Range("F43").Copy Sheet94.Range("B7")
> Range("F44").Copy Sheet95.Range("B7")
> Range("F45").Copy Sheet96.Range("B7")
> Range("F46").Copy Sheet97.Range("B7")
> Range("F47").Copy Sheet98.Range("B7")
> Range("F48").Copy Sheet99.Range("B7")
> Range("F49").Copy Sheet100.Range("B7")
> Range("F50").Copy Sheet101.Range("B7")
> Range("F51").Copy Sheet102.Range("B7")
> Range("F52").Copy Sheet103.Range("B7")
> Range("A1:A2").Select
> End With
> MsgBox "New stock numbers aplied"
> End If
> End Sub
>
> Here is code I get an error with, is there a work around for this?
>
> Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As
> Range)
> Set t = Target
> Set d = Range("D7:D36")
> If Intersect(t, d) Is Nothing Then Exit Sub
> If t.Value = "" Then Exit Sub
> Sheets(1).Activate
> Range("A1").Select
> End Sub
>
> Many thanks for your help
>
> Mark
>
> "Don Guillett" wrote:
>
>> 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
>>
>> .
>>

First | Prev | Next | Last
Pages: 1 2 3
Prev: Index/Match vs VLookup?
Next: Lookup for Part of the number