Dont paste blank cells.
-
homegrownandy -
August 17, 2018 at 1:08 PM -
Thread is marked as Resolved.
-
-
-
If you want the copy to have the (displayed) values but not formulas, use the PasteSpecial method with an xlPasteType of xlPasteValues.
-
thanks for the reply, I dont believe I can do that.
Code
Display MoreSub ExcelDataToWord() Dim objWord As Object Dim ws As Worksheet Dim lngLastRow As Long On Error GoTo Errorcatch lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row Set ws = ThisWorkbook.Sheets("RISKS") Set objWord = CreateObject("Word.Application") objWord.Visible = True 'Optimize Code Application.ScreenUpdating = False Application.EnableEvents = False ws.Range("A4" & ":H" & lngLastRow).Copy '------------browse--------------- Dim fileExplorer As FileDialog Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker) '--------------------defaulting folder------------------------ With fileExplorer .Title = "Select a Folder" .AllowMultiSelect = False .ButtonName = "Select" .InitialView = msoFileDialogViewList .InitialFileName = "\\server\general\RAMS\RAM_RAMS" If Right(strName, 1) <> "\" Then strFolder = strFolder End If If .Show <> -1 Then Exit Sub Else strFolder = .SelectedItems(1) End If End With '--------------------defaulting folder------------------------ '------------browse--------------- 'open the word doc 'objWord.Documents.Open "C:\Users\name\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required objWord.Documents.Open Filename:=strFolder 'pastes the value of cell at the bookmark With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable HeadingFormat = True End With 'Optimize Code Set objWord = Nothing Application.ScreenUpdating = True Application.EnableEvents = True 'Clear The Clipboard Application.CutCopyMode = False Exit Sub Errorcatch: Debug.Assert False MsgBox err.Description ' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove Resume End Sub
so its this line:
I can look at other paste options but this is pasting into a table and other options have formatting issues. What do you think?
Andy.
Update:
If you don't think there's a "one line" solution here I could add code that copies this data to a new sheet first and then carries on with the rest of the code.
-
Sorry, I assumed you were copying into an Excel range. How about using PasteSpecial to a temporary range, copying that range into Word, and then deleting the temporary range?
-
Thats what ill do, Thanks.
-
-
Using the below code, i still get a page full of zeros instead of blanks.
Code
Display MoreSub RangePasteTest() Dim Ticker As Range Sheets("RISKS").Activate Set Ticker = Range(Cells(4, 1), Cells(65, 8)) Ticker.Copy Sheets("Paste Special").Select Cells(4, 1).PasteSpecial xlPasteValues End Sub
Is there anything obviously wrong there?
Thanks, Andy
-
Are you able to copy/paste a filtered range into word?
-
Do these formulas evaluate to an empty string("") or to zero but your sheet (Options, Advanced, Display options for this worksheet) or cell number format is set to not display zeros? Using xlPasteValues with formulas that evaluate to "" leaves the copied formula cells empty.
-
Dave - I'm unsure but unwilling to test that at the moment (It's taken weeks to get the paste into a word table working as I want). I could try this approach to put the rows I want onto a new sheet though.
Johnathan - you are correct I had turned off show zeros. I have put them back on now.
FYI: this is the formula in each cell in the table
Thanks, Andy
-
-
-
I havn't tested this but it seems thats what im looking for, It will take me a while to get all the references correct. I'll do that tomorrow.
i did get it working using this:
CodeActiveSheet.Range("A4" & ":H65").Select For Each Cell In Selection If Cell.Value = 0 Then Cell.Value = "" End If
But it was very slow at deleting a page of 0's.
Really appreciate your help. This was the final stage in a long project of mine. Happy to be near completion!
Cheers, Andy.
-
That will overwrite your formulas, so I doubt that you actually want to do that. If so, this will be faster:
-
I got carried away and finished it today. Works perfect,
Thanks again
Just a comment on over writing formulas, I was doing it after the paste so no issue there bar the speed.
Your suggestion is a lot better.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!