sschwant
02-29-2020, 07:03 AM
I have a mystery that I am struggling to solve. Can't figure this one out and I'm hoping someone might have some troubleshooting ideas. I'm running a macro to copy data from "Sheet1" of my work book to "WPD Submission Form". The data I'm copying is coming from a web based proprietary ticketing system. The data is obtained by first hitting Print on the web based ticketing system which formats the data as a clean two column table which makes it easy to highlight and copy, then paste as TEXT to Sheet1 of the Excel file (into A1 so that all the data to be copied is in column B of Sheet1). The mystery is that I am copying the same field (Executive Sponsor) twice into the Submission Form; once into Range D6:E6 and once into Range I31:J31. Code snippets below.<br><br>
<br>'EXECUTIVE SPONSOR<div> Sheets("Sheet1").Activate</div><div> Range("B71").Copy</div><div> Sheets("WPD Submission Form").Activate</div> Range("D6:E6").PasteSpecial<br><br><br>Result 1 = 12/26/2019<span style="white-space: pre;"> </span><div><br></div><br>
<br><div>'EXECUTIVE SPONSOR</div><div> Sheets("Sheet1").Activate</div><div> Range("B71").Copy</div><div> Sheets("WPD Submission Form").Activate</div><div> Range("I31:J31").PasteSpecial</div><br><br>Result 2 = proper text value for example my name: Steve Schwantes<br><br>I have attempted to check all possible issues within the Form itself; cell formatting, data validation, conditional formatting, etc., to no avail. I even tried implementing different formatting using VBA. No luck there either. Finally I tried to eliminate the fault by deleting the first string of code (which appears first in the flow) and then after the second string using the following code:<br><br>
<br> Range("D6:E6").Value = Range("I31:J31").Value<br><br><br>That final solution actually worked. However, I still have no clue as to why the first set of code kept resulting in a # or date range which I am not able to convert to the expected name by changing the cell format to text; this is the result of trying to do that - <table border="0" cellpadding="0" cellspacing="0" style="width: 257px" width="257"><tbody><tr height="19" style="height:14.5pt">
<td colspan="2" height="19" class="xl67" width="257" style="border-right:.5pt solid black;
height:14.5pt;width:193pt"><br>43825.51512<br><br>So, while I have a workable alternate solution - I will still post in the hope of getting any insights to this mystery.<br><br>Thanks!<br>Steve</td></tr></tbody></table> <br>
<br>'EXECUTIVE SPONSOR<div> Sheets("Sheet1").Activate</div><div> Range("B71").Copy</div><div> Sheets("WPD Submission Form").Activate</div> Range("D6:E6").PasteSpecial<br><br><br>Result 1 = 12/26/2019<span style="white-space: pre;"> </span><div><br></div><br>
<br><div>'EXECUTIVE SPONSOR</div><div> Sheets("Sheet1").Activate</div><div> Range("B71").Copy</div><div> Sheets("WPD Submission Form").Activate</div><div> Range("I31:J31").PasteSpecial</div><br><br>Result 2 = proper text value for example my name: Steve Schwantes<br><br>I have attempted to check all possible issues within the Form itself; cell formatting, data validation, conditional formatting, etc., to no avail. I even tried implementing different formatting using VBA. No luck there either. Finally I tried to eliminate the fault by deleting the first string of code (which appears first in the flow) and then after the second string using the following code:<br><br>
<br> Range("D6:E6").Value = Range("I31:J31").Value<br><br><br>That final solution actually worked. However, I still have no clue as to why the first set of code kept resulting in a # or date range which I am not able to convert to the expected name by changing the cell format to text; this is the result of trying to do that - <table border="0" cellpadding="0" cellspacing="0" style="width: 257px" width="257"><tbody><tr height="19" style="height:14.5pt">
<td colspan="2" height="19" class="xl67" width="257" style="border-right:.5pt solid black;
height:14.5pt;width:193pt"><br>43825.51512<br><br>So, while I have a workable alternate solution - I will still post in the hope of getting any insights to this mystery.<br><br>Thanks!<br>Steve</td></tr></tbody></table> <br>