Copy Variable Value to another sheet and append

[GeeWhiz7]

Hi folks,
I'm wondering if anyone can help me figure out why this part of my code is not working...
It is inside a bigger bit of code, but this is the bit that I have been working on.

What I want it to do is

• to copy the variable values assigned (from sheet 4) above
• to a different sheet (sheet 1) and append below any copied earlier.
• below I was testing a direct copy of cell B2, but I would prefer to use the variables n through s

It all runs with no error, but doesn't do anything either and the destination sheet 1 stays unchanged.

...

 Dim n As Double
 Dim m As Double
 Dim o As Double
 Dim p As Double
 Dim q As String
 Dim r As Double
 Dim s As Double
 
 Dim addRow As Long
 
 n = Sheet4.Range("H196").Value
 m = Sheet4.Range("M194").Value
 o = Sheet4.Range("L55").Value
 p = Sheet4.Range("T60").Value
 q = Sheet4.Range("B2").Value
 r = Sheet4.Range("C18").Value
 s = Sheet4.Range("L16").Value
 
' MsgBox "Values " & n & " " & m & " " & o & " " & p & " " & q & " " & r & " " & s
 
 If n > 0.25 And m > -0.15 And o > 8 And p < 96 Then
   
     addRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).row
 
     Sheet4.Range("B2").Copy Sheet1.Range("B3" & addRow + 1)
   
     Call CreatePowerPoint
   
 End If

 

[Joe4]

You need to remove the number after column B in your copy row:

     Sheet4.Range("B2").Copy Sheet1.Range("B" & addRow + 1)

 

[GeeWhiz7]

You need to remove the number after column B in your copy row:

     Sheet4.Range("B2").Copy Sheet1.Range("B" & addRow + 1)

Hi Joe, thanks for the idea. I removed it and still get nothing occurring on the destination sheet 1. Could it be something related to not activating the sheets?

 

[Joe4]

It most likely means that at least one of these conditions is not being met:

 If n > 0.25 And m > -0.15 And o > 8 And p < 96 Then

so you should check each one.

Do you know how to step into your code? If you do that, and proceed through the code one line at a time using the F8 key, you can easily see if these conditions are being met or not by watching the path your code takes when you test it.

Alternatively, you could add a simple Message Box to see if it is being met, i.e.

 If n > 0.25 And m > -0.15 And o > 8 And p < 96 Then
    MsgBox "Conditions met"
    'Rest of code here

If you run your code and do NOT see that message box pop-up, then you know your IF statement is evaluating to false for some reason.

 

[GeeWhiz7]

It most likely means that at least one of these conditions is not being met:

 If n > 0.25 And m > -0.15 And o > 8 And p < 96 Then

so you should check each one.

Do you know how to step into your code? If you do that, and proceed through the code one line at a time using the F8 key, you can easily see if these conditions are being met or not by watching the path your code takes when you test it.

Alternatively, you could add a simple Message Box to see if it is being met, i.e.

 If n > 0.25 And m > -0.15 And o > 8 And p < 96 Then
    MsgBox "Conditions met"
    'Rest of code here

If you run your code and do NOT see that message box pop-up, then you know your IF statement is evaluating to false for some reason.

Joe, you were right, one of the conditions wasn't being met.
So now it works.
Thank you for your help and using F8 was very useful.

 

[Joe4]

You are welcome!
Glad I was able to help.

 

[GeeWhiz7]

You are welcome!
Glad I was able to help.

Hi Joe, Something weird has come up and I'm not sure how to fix.
The bit of code you helped with was working fine and now it seems to have stopped.
I put it in a separate macro to be called from another and have tested it by itself with the same problem.

What is strange is that the clipboard contents contain the copied data (I can paste to notepad for example), but the destination cells never receive it and they stay blank.
I don't know if you can see anything problematic from what is below. I have zero idea what the issue might be.

Sub CaptLoopResults()
 
Dim addRow As Long
 
Application.ScreenUpdating = False

DoEvents
   
     addRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).row
 
     Sheet4.Range("C18").Copy 'Date
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
     
     Sheet4.Range("B2").Copy
     Sheet1.Range("B" & addRow + 1).PasteSpecial Paste:=xlPasteValues 
     
     Sheet4.Range("H196").Copy
     Sheet1.Range("C" & addRow + 1).PasteSpecial Paste:=xlPasteValues 
     
     Sheet4.Range("J196").Copy
     Sheet1.Range("D" & addRow + 1).PasteSpecial Paste:=xlPasteValues  
     
     Sheet4.Range("M194").Copy
     Sheet1.Range("E" & addRow + 1).PasteSpecial Paste:=xlPasteValues 
     
     Sheet4.Range("Q195").Copy
     Sheet1.Range("F" & addRow + 1).PasteSpecial Paste:=xlPasteValues  
     
     Sheet4.Range("Y199").Copy
     Sheet1.Range("G" & addRow + 1).PasteSpecial Paste:=xlPasteValues  
     
'     Sheet4.Range("B200:B238").Copy
'     Sheet1.Range("H" & addRow + 1).PasteSpecial Transpose:=True 
     
     Sheet4.Range("H200:H238").Copy
     Sheet1.Range("H" & addRow + 1).PasteSpecial Transpose:=True 
     
     Sheet4.Range("M200:M238").Copy
     Sheet1.Range("AU" & addRow + 1).PasteSpecial Transpose:=True 
     
'     Sheet4.Range("N200:N238").Copy
'     Sheet1.Range("G" & addRow + 1).PasteSpecial Transpose:=True 
     
'     Sheet4.Range("O200:O238").Copy
'     Sheet1.Range("G" & addRow + 1).PasteSpecial Transpose:=True 
     
'     Sheet4.Range("R200:R238").Copy
'     Sheet1.Range("G" & addRow + 1).PasteSpecial Transpose:=True 

Application.ScreenUpdating = True
   
End Sub

 

[Joe4]

Did you try stepping through the code to see what happens, where it goes in the code, and if it bails out too early?

 

[GeeWhiz7]

Did you try stepping through the code to see what happens, where it goes in the code, and if it bails out too early?

It pretty much doesn't get past the first copy operation, it does select it and looking at the clipboard it has copied it.
Then it doesn't paste it, but it does pick up the second range even though F8 doesn't advance to the second range copy (visually anyway).

     Sheet4.Range("C18").Copy 'Date
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues

 

[Joe4]

When you step through your code, when you get to the paste line, hover over the "addRow" variable. This will show you the value of the variable at that time.
Is it what you expect?