How to paste a value

[mperrah]

This one sounds easy.
I have a macro to paste data from one sheet to another.
The problem is the data is an output from a web page
it pastes the numbers as ="0000"
I need to be able to search and sort.
my macro has "ActiveSheet.Paste" as the code to place the data.
would adding paste.values work
or do I need to trim the =" before and the trailing "
to get the number in the format 0000 on the resulting paste
without the =" "

thanks for your help

Mark

[Oorang]

Hi Mark,
If pastespecial xlPasteValues doesn't clean it up for your, you are probably going to need to clean it up yourself. There are a couple of approaches to this problem. Have you tried just importing the page using data>import external data> new web query?
I might be able to give you some more specific direction if you provided the URL so I can see the web-page structure.

[mdmackillop]

Try

MyStr = "=2457"
MyValue = Val(Replace(MyStr, "=", ""))

[mperrah]

This is what I have (I not the best coder, this came from help in this forum)
The column "N" needs to be pasted as the value. the other cells dont matter the format. I not sure where to stick the .pastespecial


Sub AddToDetail()
Dim sh_source As Worksheet
Dim sh_dest As Worksheet
Dim cell As Range
Set sh_source = Worksheets("Data")
Set sh_dest = Worksheets("QCDetail")
For Each cell In sh_source.Range("C3:C" & sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & cell.Row).Copy sh_dest.Range("B" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1)
.Range("N" & cell.Row).Copy sh_dest.Range("A" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row) ' paste values?
.Range("E" & cell.Row).Copy sh_dest.Range("D" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row)
.Range("I" & cell.Row).Copy sh_dest.Range("E" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row)
.Range("P" & cell.Row).Copy
sh_dest.Range("C" & sh_dest.Range("B" & _
Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues
End With
End If
Next cell
End Sub

[Charlize]

That piece of coding looked familiar. So I think that this is what you want :

.Range("N" & cell.Row).Copy
'just copy value and not the formatting
sh_dest.Range("A" & sh_dest.Range("B" _
& Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues

[mperrah]

Hello Charlize ;)
I tried pasting your code in but I get a compile error "Expected: end of statement"

Glad to see you're online
Mark

[mperrah]

mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87), I'm not sure how to incorporate your code,
I'd like to test it. still having trouble with other posts.

Mark

[mperrah]

I've tried recording a macro to convert a value to a number but the recorder doesn't capture these steps.

[Charlize]

This is what I have (I not the best coder, this came from help in this forum)
The column "N" needs to be pasted as the value. the other cells dont matter the format. I not sure where to stick the .pastespecial


Sub AddToDetail()
Dim sh_source As Worksheet
Dim sh_dest As Worksheet
Dim cell As Range
Set sh_source = Worksheets("Data")
Set sh_dest = Worksheets("QCDetail")
For Each cell In sh_source.Range("C3:C" & sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & cell.Row).Copy sh_dest.Range("B" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1)
'lines of me ...
.Range("N" & cell.Row).Copy
'just copy value and not the formatting
sh_dest.Range("A" & sh_dest.Range("B" _
& Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues ' paste values?
'end of lines of me
.Range("E" & cell.Row).Copy sh_dest.Range("D" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row)
.Range("I" & cell.Row).Copy sh_dest.Range("E" & _
sh_dest.Range("B" & Rows.Count).End(xlUp).Row)
.Range("P" & cell.Row).Copy
sh_dest.Range("C" & sh_dest.Range("B" & _
Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues
End With
End If
Next cell
End Sub
Give an example of the data that you fetched from the web. Before you alter anything to the raw data. So we can see what it is that you exactly want to format. If possible, also provide the code you have to format the raw data.

[mperrah]

Thanks Charlize,
I zipped the file I'm working with.
The "Raw" sheet is the way the data comes off the website.
The "Data" sheet has all the macro buttons.
I click "Update jobs" first. then check the files I want to QC.
then i click "QC List" to print a list of jobs to check.
Then I uncheck the jobs I couldnt complete on the "Data" sheet
and then click "Add to Detail".
Here I update the failed items in the 1-27 range of the "QCDetail" sheet.
Then I click "Add to Archive" and update the failed items here as well.
I email this file to HQ where they print the finalized qc forms from the archive.

If I could get the archive to update the failed items from the qcdetail page would be great.

Most important is the "raw" sheet shows each cell's value is in the ="value" format and my formulas need to have just value format.

The modules for AddToDetail, AddToArchive, and UpdateJobs could be modified to copy and padte special just values, and then convert just the tech number to number format (not text).
Mark
:think:

[mdmackillop]

To get rid of "=", run this on the Raw sheet

Sub LoseEquals()
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.Cells
If IsNumeric(cel) Then
cel = Val(Replace(cel, "=", ""))
cel.NumberFormat = "0"
End If
Next
Application.ScreenUpdating = False
End Sub

[mperrah]

mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87), Tried this code, it rounds the 16 digit work order numbers.
the last three digits become zero (hundreds)
I got the code to work from Charlize post #9 and it removes the ="".
And it does seem to calculate the formulas in this state so we found a solution.
New issue>>
Is there a way to make the named ranges dynamic?
so if I delete a row of data everything doesnt go hay wire.

I have a weeknum function at the end of the rows on the "QCDetail" sheet. and the "Pass" and "Fail" and "TechID" named ranges sometimes change by insert or delete.
can the range be dynamic instead of typed in so inserting or delete will update on the fly?
Thanks again for your help.
Mark

[mperrah]

Also,
how do i test if i have already ran a macro.
If i use "UpdateJobs" the column "N" will be empty.
I can simply test if "N" is empty then exit sub else continue if not
just not sure on what to put where.
Mark

[mdmackillop]

Try using the Offset function in the Name. I'm using COUNTA on column A as all cells contain data and will return a usable value. eg for Fail

=OFFSET(QCDetail!$G$8,0,0,COUNTA(QCDetail!$A:$A),1)
Due to the cells with data above A8, this will select additional cells (3) below the bottom line. You could build in -3 to the COUNTA or amend the COUNTA range

=OFFSET(QCDetail!$G$8,0,0,COUNTA(QCDetail!$A$8:$A$8000),1)

[mikerickson]

If everything in column N is in that format (starting with an =) then replace that line in your code with this and you should be good to go.


sh_dest.Range("A" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row) = _
Evaluate(.Range("N" & cell.Row))

[mperrah]

mikerickson,
I get an error statement when using your code.
The Tech numbers and the qcdate are the numbers that have to be numbers, they copy from the "raw" sheet" first to the "Data" sheet
then to the "QCDetail" sheet. On the QCDetail page I use the weeknum function to populate the total pass and fail number in "F4" and "G4" a summary page on another sheet and also chart. The weeknum works if the numbers are stored as text, but not with the ="xxx" format.

Also, I just noticed in the select object tool that there are many (100+) empty text boxes on several of my sheets. Do they need to before for the macros, do the macros make them. If they aren't needed how can remove them quickly (vba?)

Help...
Mark

[mperrah]

mdmackillop, I tried using the naming scheme you posted #14 but my formulas show a value error. I read the link you gave me about sumproduct and I think all the items being in the sum need the same range length. I think I need a way to make the cell values for the weeknum() need to be made in vba or be added to the dynamic naming so all parts of the sumproduct show as the same length. ?
Maybe it is something else...
I'll zip my latest.

Mark

[mperrah]

Here is the latest.
Also can you show me how to get rid of the empty text boxes (or are they needed?) I think they get created when I use the AddToDetail macro?: pray2:
Mark

[mikerickson]

Sub test()
Dim xObj As Object
For Each xObj In ThisWorkbook.Sheets("sheet1").OLEObjects
xObj.Delete
Next xObj
End Sub



Save your workbook first. There is no undo.

[mperrah]

Thank You Mikerickson,

I have been working in Excel 2007 and found the selection tool and it let me only delete one text object at a time. This is the first place that let me know the text boxes were there...

My desktop has 2003 and I found go to.. then clicked special and Objects was a choice.
When I clicked that all the text boxes were highlited so I hit delete.
I ran your sub as well to make sure nothing was missed. Thank you for your help.

[mperrah]

mdmackillop, regarding the named range. If I were to start the range at G1
instead of G8 would it work. The range is used to calculate part of the sumproduct and the it looks for an "x" specificaly, if other stuff is in the range I don't think it would matter for the formula to work, and then naming it this way would help If a row gets added or deleted.
Am I on the right track?


=OFFSET(QCDetail!$G$1,0,0,COUNTA(QCDetail!$A:$A),1)


Mark

[mdmackillop]

You can't go to this range by selecting the name from the Name box, so create a macro to select the named range. I do this to ensure things are working correctly.

[mperrah]

How do I do this (macro to slect a named range)?, can you give me a sample or point me to another knowledge base. the sumproduct you pointed out has been very helpful.
Thanks \Mark

[mdmackillop]

Sub Test()
Range("MyRange").Select
End Sub

[mperrah]

mdmackillop,
I renamed the ranges in the name editor to look from 8 to 999
The formulas work ok.
One issue is the weeknum helper column.
Is there a way to make the helper column in vba so it doesnt have to be typed. I type the code =weeknum(c8) and copy down to row 999
If I delete cells and shift up, the formula stops working.
ie. the weeknum(c199) looks in c199 for the date, if I delete c199 and shift up the formula thinks c199 no longer exists c200 comes after c198.
How can I make the helper column in a script or at least not care if a row was moved or deleted?

Would offset work in the helper column (or in vba not typed in the sheet preferably), so it just counts the cells over to column c where the date is and no matter what row it starts in it just looks in that same row?

If this offset would work where and how would I type in the offset formula, could it be part of a script and not have to be typed on the sheet?

Mark

[mdmackillop]

Where are you adding the Helper column, and which part of the code refers to it?

[mperrah]

I have 37 columns on a sheet.
B is "techs", C is "qcdate", F is "Pass", G is "Fail", AK is weeknum helper column. These 5 rows are named ranges that are used in updating fields based on the "wkstart" value in cell B4.
I enter data in 4 of the fields (B through G) AK is a formula copied down
=weeknum(C8)

[mperrah]

=SUMPRODUCT(--(Pass="x"),--(QCDate=WEEKNUM(WkStart)))

This is how I calculate the pass total based on the wkstart value


=SUMPRODUCT(--(Fail="x"),--(QCDate=WEEKNUM(WkStart)))

Fail values come from this.
If I delete a row the values show #REF! error.
You showed me the data for a sumproduct have to be of similar range numbers (all 999 rows or all 87 rows, same number of rows)

[mperrah]

Also, I have a sub that edits columns then copies and pastes the results
I dont want it run twice because it works of the raw data, once the data has been edited the sub will fail. The value for cell n1 would be "CA" if the sub has been run.
can I add a code like below to end a sub if the value of N1 is "CA"
This cod eis giving an error (Type Mismatch?)

Option Explicit

Sub UpdateJobs()
If ThisWorkbook.Sheets("Raw").Cells("M1").Value = "CA" Then Exit Sub

[mdmackillop]

This should create the range name "WkNum" and add the formula to it. I'd avoid using the Function as the name to avoid confusion.


Sub Macro1()
ActiveWorkbook.Names.Add Name:="WkNum", RefersTo:= _
"=OFFSET(QCDetail!AK8,0,0,COUNTA(QCDetail!A:A),1)"
Range("AK8").Formula = "=WEEKNUM(C8)"
Range("wknum").FillDown
End Sub

[mperrah]

got the cell value test to work

If Sheets("Raw").Cells(1, 13).Value = "CA" Then Exit Sub
mdmackillop,
Do I need to run this macro whenever the cells are changed?
Can I put it in the worksheet change event to keep the cell values updated if the row info changes?

[mperrah]

Or can I write the name add event for all the values used for the sumproduct, this way all of them haveb the same length.
The values used are techs, qcdate, pass, fail, weeknum and wkstart
Would it work to alter your code for each of these values to use the selection change or sheet change etc to constantly keep the named ranges up-to-date with the values entered in the range of cells?
- I think were on the right track -
Mark

[mperrah]

Would this work to update thePass named range?
And couls I call All the Add Named Ranges in one sub?

Sub AddPass()
ActiveWorkbook.Names.Add Name:="Pass", RefersTo:= _
"=OFFSET(QCDetail!F8,0,0,COUNTA(QCDetail!A:A),1)"
End Sub

[mperrah]

Here is the sample I'm working with.
It's one sheet from a workbook.
I have the named ranges of techs, qcdate, pass, fail,
wkstart refers to a cell (C2)
and weeknum is a formula in column AJ


=SUMPRODUCT(--(Pass="x"),--(QCDate=WEEKNUM(WkStart)))

cell (F2) sums the Pass rows with an x in the weeknumber range
cell (G2) sums the Fail rows

As I add rows of data pasted with a macro the named range needs to expand to account for the new data.
Occasionaly I have to remove a row
and the named range needs to reflect this
so the sumproduct formula will still work.

[mperrah]

I have an almost working file. I had a version that paste the values and number formats, but I added a lot of info to the paste and now the dates paste as serials, or not at all.

This works in one module but I can't paste to work in another.

Selection.PASTESPECIAL Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


.Range("AS" & cell.Row).Copy _
sh_dest.Range("C" & sh_dest.Range("B" _
& Rows.Count).End(xlUp).Row) 'here is where I'd like to have values and number formats pasted
End With

The function is working a bit different and I'm not sure the syntax to work here.
I've tried recorded macros to no avail.
Any suggestions.
The module in question is "AddToDetail" of my zipped file.

Thanks for your wisdom.
Mark

[mperrah]

Also, in excel 2007, i use compatability
to let my 2003 co-workers view my reports.
The checker finds 2 cell formats that are incompatible.
How do I find exactily what formats are no good so I can change them?
Thanks in advance.
Mark

[mperrah]

For Each cell In sh_source.Range("C3:C" & sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & cell.Row).Copy sh_dest.Range("B" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1)
.Range("N" & cell.Row).Copy sh_dest.Range("A" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row)
End With
End If
Next cell

this snippet is a cell I'm trying to copy and paste a date and a number
the date is part of an if statement and I don't want the formula to paste just the evaluated result (just the value of he cell)


=IF(Data!$O$3="CA",qcdone,"")
this is the code being copied.
If i delete or change what's in "O3" everything updates all wrong.
I don't want a link or dynamic data in the pasted cell.
Just need the value pasted
the vba above selects the source data from a list so if I can modify this code to copy and paste just the value would really help.

I'm in the experts hands... : pray2:

[Charlize]

For Each cell In sh_source.Range("C3:C" & sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & cell.Row).Copy
sh_dest.Range("B" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1).Paste xlPasteValues
.Range("N" & cell.Row).Copy
sh_dest.Range("A" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row).Paste xlPasteValues
End With
End If
Next cell

[mperrah]

Hello Charlize,
Your code makes an error:
Compile error
syntax error
expecting end of line...

I moved the line break and now it says "object doesn't support this method

[mperrah]

I recorded a macro that made this and it pastes the numbers for the date ok, but the number pastes as text.
How can I paste it as a number instead of text?


.Range("C" & cell.Row).Copy
sh_dest.Range("B" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("AT" & cell.Row).Copy
sh_dest.Range("C" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[mperrah]

Sub convert_text_to_number()
Worksheets(1).Range("A2:A" & Worksheets(1).Range("A" & _
Rows.Count).End(xlUp).Row).Select
Selection.NumberFormat = "0"
End Sub


Selection.Copy
Sheets("Data").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Data").Range("C3:C999").Select
Selection.NumberFormat = "0"

This still isn't working, I'm lost :help

[Charlize]

Sorry, had to be pastespecial instead of paste.

For Each cell In sh_source.Range("C3:C" & sh_source.Range("C" & Rows.Count).End(xlUp).Row)
If cell.Offset(, -2).Value = "a" Then
With sh_source
.Range("C" & cell.Row).Copy
sh_dest.Range("B" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
.Range("N" & cell.Row).Copy
sh_dest.Range("A" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial xlPasteValues
sh_dest.Range("A" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row).PasteSpecial xlPasteFormats
End With
End If
Next cell

[Charlize]

put this on top :
Dim help_number As Longand this instead of the copy and paste routine for the technumber

help_number = .Range("C" & cell.Row).Value
sh_dest.Range("B" & sh_dest.Range("B" & Rows.Count).End(xlUp).Row + 1).Value = help_number

[mperrah]

I tried the code below from the excell help on text to numbers and it seems to work..
After the numbers "as text" get pasted I added this code to re-paste...

I'll test your codes out too cause they look nicer.
Thank you soo much for all your help with this project Charlize.
You rock.
Mark

Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False