VBA Help

[Chippa]

Hey everyone,

I am super new to vba and am trying to get started to help speed things along with some study work. Firstly I am trying to create a macro that runs based on the value of a cell, and I need that macro to copy and paste data at the start of a loop. I have made the copy/paste macro by recording, now I just need the automatic start.

My code for the copy/paste is;

Sub Run()
'
' Run Macro
'
' Keyboard Shortcut: Ctrl+m


Range("s91:s103").Select
Selection.Copy
Range("G91:G103").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

And I need it to run if cell U104 = 1.

Can anyone help? Also can someone point to a good book or notes to help get me started in doing this myself.

Cheers

Dave

 

[Tom Urtis]

Please explain how a 1 gets into cell U104. Is it manually entered? Does a 1 pop up from a formula in U104? Is it from a web query or from copying and pasting from elsewhere?

 

[Rick Rothstein]

First off, since you are only copy values, there is no need to use 4 code lines to do it... you can assign the values of one range directly to another. So, the 4 code lines you posted can all be replaced by this...

Range("G91:G103").Value = Range("S91:S103").Value

Now, to run that only when U104 equals 1 takes a simple If..Then statement...

If Range("U104").Value = 1 Then
  Range("G91:G103").Value = Range("S91:S103").Value
End If

As for the "automatic start", I have the same question as Tom posted.

 

[Tom Urtis]

I am thinking by his reference of "automatic start" it will be an event code such as Change or Calculate at the worksheet level, per my questions, but we'll see.

 

[Chippa]

Please explain how a 1 gets into cell U104. Is it manually entered? Does a 1 pop up from a formula in U104? Is it from a web query or from copying and pasting from elsewhere?

Hi,

Cheers for the reply. The 1 is obtained from an 'if' statement, the data the answer at the end of my calculations is the re-calculated until the 1 becomes a 0, then it will stop. Does that make some sense?

Cheers

 

[Tom Urtis]

Sounds like you have a formula in cell U104 that sometimes can produce a 1.

Try this, right-click your worksheet tab and left-click to select View Code. Paste the below procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Calculate()
If Range("U104").Value = 1 Then Range("G91:G103").Value = Range("S91:S103").Value
End Sub