# Solved: Excel conditional macro



## MBInDe (Oct 14, 2009)

I am trying to write a macro that will copy data from on location to another if the value in a particular cell is greater than -0-. Below is my attempt which isn't working. Any help would be greatly appreciated.
MBInDe

Sub testz()
'
' testz Macro
' Macro recorded 10/20/2009 by MBInDe
'
' Keyboard Shortcut: Ctrl+z
'
If Cell("A33") = >0 Then
Range("G6:G13").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
End If
End Sub


----------



## computerman29642 (Dec 4, 2007)

Are you trying to copy the value in Cell A33 to Cells G6 thru G13?

*OR*

Are you trying to copy the value of Cell A33 to Cell G6, Cell A34 to Cell G7, etc.?


----------



## MBInDe (Oct 14, 2009)

Cell A33 contains the trigger that causes the data in cells G6-G13 to be copied to cells C6-C13. Forgot to mention previously I'm using Excel '03.


----------



## computerman29642 (Dec 4, 2007)

You do not really need any code to perform this operation. You can use a formula.

For Example, enter the following formula into Cell C6


```
=IF($A$33>0,G6,"")
```
Then drag the formula down to Cell C13.

Place the white cross on the lower right-hand corner of C6 (it should turn into a black cross), click and hold the left-button on the mouse and drag down to C13. Then release the button.

If you really want to use code to perform this operation let me know and I will help you do so.


----------



## MBInDe (Oct 14, 2009)

Thanks. Yes I would like to use code. My example was just a simple test but ultimately it will be used in a workbook where copying/pasting will be performed across many worksheets. I would like to see how conditional statements are laid out so I can build on it for future use.
MBInDe


----------



## computerman29642 (Dec 4, 2007)

Sounds like a plan. My next question is when do you want the code to run?

1. Manually run the code (via, command button, etc.)
2. The code runs when the workbook is opened (that is the only time the code will run. So, if the number in A33 changes while the workbook is opened, the code will not run)
3. When the value in A33 changes
4. Some other trigger

Here is the code that should work...


```
Dim ws As Worksheet
    
    Set ws = Sheets("Sheet1")
    
    If ws.Range("A33") > 0 Then
        ws.Range("G6:G13").Copy
        ws.Range("C6").PasteSpecial Paste:=xlPasteAll
        Application.CutCopyMode = False
    End If
```
Once you let me know how you want it to run, I can help you set it up that way (options above).


----------



## MBInDe (Oct 14, 2009)

My plan is to start it with a command button.


----------



## computerman29642 (Dec 4, 2007)

Do you need assistance?


----------



## MBInDe (Oct 14, 2009)

yes, please


----------



## computerman29642 (Dec 4, 2007)

Will this code be ran once while the workbook is opened, or multiple times? If multiple times, what do you want to happen if the value in A33 falls to 0? Are the numbers in Cells C6 thru C13 going to remain the same, become blank, etc.?

What exactly do you need help with? Do you know how to place a command button on the worksheet?


----------



## MBInDe (Oct 14, 2009)

I do know how to place a command button. See attached worksheet for more info, and examples, about this project.


----------



## computerman29642 (Dec 4, 2007)

I do not see the command button within the Excel workbook. Place the command button on the sheet you want it to go, and then re-attach the workbook.

Also, let us know exactly where you want the values to be copied from and where to paste them. Let us know what happens if the value is equal or less than 0.

Give us as much details as you can. Let us know exactly what you need us to help you with.


----------



## MBInDe (Oct 14, 2009)

Updated workbook attached.


----------



## computerman29642 (Dec 4, 2007)

Let me make sure I understand fully. Right now you are trying to match the Header in Cell C30 of the *Overview* Worksheet with the same header name in the *Store Data* worksheet.

Once that has been established, the data is copied from the *Overview* worksheet to the *Store Data* worksheet.


----------



## computerman29642 (Dec 4, 2007)

Take a look at the attached file. This should work for you.

Someone else may come along who can provide a better way to perform this task.

Let me know what you think. Test the code thoroughly on a copy version of the real workbook.


----------



## MBInDe (Oct 14, 2009)

Worked perfectly. Thanks so much.


----------



## MBInDe (Oct 14, 2009)

Worked perfectly! Thanks so much for your help.


----------



## computerman29642 (Dec 4, 2007)

No problem. :up:

Let us know if can assist any further.


----------



## MBInDe (Oct 14, 2009)

Found a small glitch in the program. See attached.


----------



## computerman29642 (Dec 4, 2007)

Let me take a look and I will get back with you.

The code stop working because it was using the *Find Function* to search for the names that where in blue on the *Store Data* worksheet.


----------



## computerman29642 (Dec 4, 2007)

Go ahead a unmark the thread as solved.

Can you explain in a little more detail what is taken place. Will you be manually changing the *Title* on the *Overview* worksheet (For Example, Feb1stNatl)?

The cells on the *Store Data* worksheet will actual be blank?

How are you hoping to determine which cells get populated with the values from the *Overview* worksheet?

Post the workbook as close to the real thing as possible. That will help greatly with the coding.


----------



## Zack Barresse (Jul 25, 2004)

So is this only that the Find() method is failing because it is not finding a matching cell? This is easily overcome with error handling. Trying to follow along.


----------



## MBInDe (Oct 14, 2009)

Can you explain in a little more detail what is taken place. Will you be manually changing the *Title* on the *Overview* worksheet (For Example, Feb1stNatl)?

There will be a formula where the column Title is located. That formula will be picking up data from initial input questions, i.e., What bank account is going to be processed? and What months activity does this bank info represent? (These questions will be answered by pressing buttons in order to make sure input is always consistent.) Therefore the Title will be changing each time a different bank or month is processed. I have added this to the Overview WS. See cells B42:E47 as well as the formula I placed in the Title cell (C30).

The cells on the *Store Data* worksheet will actual be blank?

The first time I run this program all storage ranges will be empty. After I process the activity from one bank for one month (lets assume Feb1stNatl) that storage location will be filled for a year until the next Febs 1st Natl data replaces it. At the end of 12 months of processing all storage locations will be full. From that point on new data will replace old data. (Based on this you can see that the range names I typed into the ranges of the StoreData WS would only work the first time I put data into a storage location.)

How are you hoping to determine which cells get populated with the values from the *Overview* worksheet?

All storage locations (ranges) will be the same size and each cell within the ranges will have some $$$ input even if the input is $0.00 I anticipate each range will contain approximately 40 cells and the input for every cell 1, cell 2, etc., in each range will the same, i.e., cell 1 of every range will be payroll, cell 2 in every range will be rent, etc. 

Post the workbook as close to the real thing as possible. That will help greatly with the coding.

Since there are 10 banks accounts x 12 months x 40 cells per range it would be a very large example to work with. Once you show me how to perform the task with my little example, hopefully Ill be able to adapt it to the final version.


----------



## computerman29642 (Dec 4, 2007)

Zack Barresse said:


> So is this only that the Find() method is failing because it is not finding a matching cell? This is easily overcome with error handling. Trying to follow along.


I do not believe that the Find() Method is going to be able to be used now. When the OP originally attached the sampel workbook, he had the title of the cells to be copied from the first worksheet in the cells to be pasted of the second worksheet. However, the OP has informed me that the real workbook will not be setup that way. The OP was doing that so I knew eher the data was supposed to be pasted. If you look at the latest workbook, there is nothing for the Find Method to search for on the secon worksheet.

I hope I did not confuse you more.


----------



## Zack Barresse (Jul 25, 2004)

Oh it doesn't take much to confuse me. I just don't like it when people don't post the data they actually have and then keep changing things. Makes finding a solution impossible. Good luck.


----------



## computerman29642 (Dec 4, 2007)

I thought I had everything figured out, but then I was thrown a curve ball with what the titles being taken off teh second worksheet.

Any suggestions?


----------



## Zack Barresse (Jul 25, 2004)

I generally tend to not look at these threads. They honestly waste too much of my time, which as you know is extremely limited these days. As far as looking for a solution, I generally recommend good data structure first. If that means altering what is currently in place instead of just working with it, then that is what I recommend. I know it may sound harsh of me, but I just don't have the time..


----------



## computerman29642 (Dec 4, 2007)

Not harsh at all. I know that you have a great deal on your plate, and you have very little time to spare.

No worries. I will continue to look at the issue when I have time, and hopefully help this OP find a solution.


----------



## MBInDe (Oct 14, 2009)

Computerman,

When I clicked on Edit - Goto and saw all the range names I thought that would be the key to sending data to the desired location. Apparently it isn't, so I will go back and see if I can come up with a way of typing the range names on the ranges (like the first example), and then preserving them some how even after data is copied into the ranges. This was your 1st solution and it worked. I appreciate you taking the time to assist me.

MBInDe


----------



## computerman29642 (Dec 4, 2007)

MBInDe, I really do not mind helping. I just need an example as close to the real thing as possibe so we can find the best soltuion.

Let me know if I can help you any further.


----------



## MBInDe (Oct 14, 2009)

Computerman,

I went back to the solution you gave me and discovered I could insert a row above each range (not a part of the range), i.e., a column title which would not be overwritten with new data, and your solution still worked. It is so difficult to communicate this stuff, plus I am a real novice, that I didn't pick up on what you were doing.

Thanks again,
MBInDe


----------



## computerman29642 (Dec 4, 2007)

No problem. I am just glad that we were able to find you a solution.


----------

