Copy Variable Value to another sheet and append

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

...

VBA Code:
 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
 
Last edited by a moderator:

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
Cool, it’s Another thing I did not know you could do. So when I check, the variable is as expected and matches the clipboard, but just never pastes to the destination. It even still has the dashed box around the copy location. It was working after your original recommendation post but for some reason not now. I’ve disabled add ins, done safe mode, tried separate computer etc so still lost. I’ve read some posts where these problems occur so I guess I need to keep searching for the solution.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,427
Office Version
  1. 365
Platform
  1. Windows
Let's try a few other things. Let's try selecting the sheet first, i.e.
VBA Code:
     Sheet4.Range("C18").Copy 'Date
     Sheet1.Activate
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
Does that work?
Do you get any errors?
If so, what are they?
 

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Let's try a few other things. Let's try selecting the sheet first, i.e.
VBA Code:
     Sheet4.Range("C18").Copy 'Date
     Sheet1.Activate
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
Does that work?
Do you get any errors?
If so, what are they?
It still had the issue so I deleted the destination sheet added a new one and changed the code name and now it is working again. I'm not sure why that worked, but maybe something in the sheet was causing an issue?? Anyway, happy to be up and running and thank you Joe4, your help is much appreciated.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,427
Office Version
  1. 365
Platform
  1. Windows
You are welcome.

Sounds like "Sheet1" may not have been the proper sheet reference.
Note that you can also reference sheets specifically by name, i.e.
VBA Code:
Sheets("SheetName").Activate
 

Forum statistics

Threads
1,175,457
Messages
5,897,531
Members
434,660
Latest member
Stoyf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top