clean up code a little

[terilad]

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

 

[JLatham]

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]

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

 

[Don Guillett]

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

 

[terilad]

I am starting out on stock maintenance sheet

Mark

 

[terilad]

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]

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.

 

[JLatham]

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]

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("D736")
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]

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

 

[terilad]

The error hapens when I run the macro ApplyStock


Mark

 

[Don Guillett]

See my previous msg about sending file.
I can't take the time to guess

 

[terilad]

Only problem being it is a very large file to try and send.

I will give it a go.

Thanks


Mark