PDA

View Full Version : Macros



gemmalouise
12-11-2006, 03:47 AM
hello

i am trying to get my program so that if a certain cell displays the word "winner" that it copies that cell into another worksheet to save it has history, i have tried several ways of doing but i cant seem to get it to work, it would be great of anyone has any ideas of working this out.

here is my coding so far:



Range("A8:P13").Select
Selection.copy
Sheets("Winner Log").Select
Sheet3.Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Numbers").Select
Sheet1.Range("E23").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheet1.Range("A5:G5").Select
Selection.copy
Sheets("Winner Log").Select
Sheet3.Range("Q4:W4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheet3.Range("Q5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheet3.Range("Q6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheet3.Range("Q7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheet3.Range("Q8").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheet3.Range("Q9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Numbers").Select
Sheet1.Range("D25").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

thanks

JimmyTheHand
12-11-2006, 04:11 AM
:hi: Welcome to VBAX!


Could you upload a sample xls that contain both before, and after, macro state?
It's not quite clear what cells need to be copied to where.

Jimmy

CBrine
12-11-2006, 01:15 PM
Jimmy,
I think you need a certain number of posts before you can upload any files.

GemmaLousie,
Here's what I got from your post.
You want to copy data from your main sheet to a closed workbook, when a certain cell on your main workbook equals "winner"

1. Which cell is it?
2. Is "Winner" the result of a formula, or is it entered manually into the cell?
3. What data do you want to copy to your history workbook?
4. What is the name of the history workbook?

Will need these answers before we can proceed.

HTH
Cal

Cyberdude
12-11-2006, 01:58 PM
Hi, CB! Glad to have you join us.

As an aside, I want to encourage you to reduce or eliminate as many "Select"s as you can. It will increase efficiency of your code and make it easier to read.

For example, if you want to copy the contents of cell "A1" on a sheet named "ABC" to cell "B1" on another sheet named "XYZ", you can write something like this:
With Worksheets("ABC")
Range("A1").Copy Worksheets("XYZ").Range("B1")
End With The idea is to use the "With ..." to act as a select statement substitute. Note that it isn't necessary to preselect the target sheet before doing the copy.

Also try to put as much into one statement as you can.
When performing a "Copy", use the format:

Range("A1").Copy Range("B1")

Also take advantage of the default value of statements. Instead of:
Selection.PasteSpecial Paste:=xlValues,Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Use the shorter:
Selection.PasteSpecial Paste:=xlValues

I'm sure other forum members will have similar suggestions for simplifying your code.

Hope this helps,
Sid

mdmackillop
12-11-2006, 02:58 PM
Hi Gemma,
Welcome to VBAX
I'm guessing from your code; is it something like this that you're after?
Regards
MD

gemmalouise
12-12-2006, 04:12 AM
thank you for your replies, i am pretty new to VBA so somethings i dont understand.

my program is a lottery syndicate , and i have to design a program that records the history of the lottery numbers, and records the winners, and only the winners, i can get my program to record the winners and the losers, but not just the winners.

gemmalouise
12-12-2006, 04:16 AM
i have attatched my spreadsheet

mdmackillop
12-12-2006, 06:19 AM
Much simpler with an example!

Option Explicit

Sub CopyWinners()
Dim Cel As Range, Tgt As Range
With Sheets("Numbers")
For Each Cel In .Range("A8:A13")
Set Tgt = Sheets("Winner Log").Cells(Rows.Count, 2).End(xlUp).Offset(1, -1)
If Cel.Offset(, 13) >= 3 Then
Cel.Resize(, 17).copy Tgt
.Cells(5, 1).copy Tgt.Offset(, 16)
.Cells(5, 2).Resize(, 6).copy Tgt.Offset(, 17)
End If
Next
End With
End Sub

gemmalouise
12-14-2006, 07:34 AM
GREAT IT WORKED:rotlaugh: , thanks alot.


My boyfriend is a stuck as i am, and he tried to configure the data you did for me, to his spreadsheet, it didnt work, i was wondering can you help him with his spreadsheet, i have attached it. Thanks for the help :yes