+ Reply to Thread
Results 1 to 4 of 4

Have to modify codes to bypass the clipboard

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Have to modify codes to bypass the clipboard

    Some of my macro codes are recorded macros where I copy and paste. I have run into an issue where I will hit enter in a cell after running them and it will paste previously pasted info into that cell. To eliminate this I am bypassing the clipboard. I have been successful with codes that copy and paste a single cell but am unable to figure out how to do this with codes that copy and paste multiple cells.

    Range("T2:T78").Select
     Application.CutCopyMode = False
    Selection.Copy
    Sheets("Daily Calculator").Select
    Range("k2").Select
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    The sheet I am copying this code from is the "Calculator" sheet. I have other macros that need modified but am pretty sure if I see how one is supposed to be formatted I can alter the rest, except this next one.

    Sub exporttojournal()
     
     
      Dim wrdApp As Word.Application
        Dim wrdDoc As Word.Document
        Dim Rng As Word.Range
        Dim i As Integer
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True
    
        Set wrdDoc = wrdApp.Documents.Open("C:\Users\Eli\Desktop\Journal.doc")
         ' sample word operations
        
        With Sheets("Calculator")
            Set rngWord = .Range("U1:X1", .Range("U1:X1").End(xlDown))
        End With
        
        rngWord.Copy
        
        Set Rng = wrdDoc.Range
        Rng.Collapse 0  'wdCollapseEnd
        Rng.PasteExcelTable False, True, False
        With wrdDoc
        .Content.InsertAfter "E1RM-"
        With Sheets("Calculator")
            Set rngWord = .Range("R1")
        End With
        
        rngWord.Copy
        
        Set Rng = wrdDoc.Range
        Rng.Collapse 0  'wdCollapseEnd
        Rng.PasteAndFormat (wdFormatPlainText)
        .Content.InsertParagraphAfter
        .Content.InsertAfter "Total Volume-"
        With Sheets("Calculator")
            Set rngWord = .Range("R2")
        End With
        
        rngWord.Copy
        
        Set Rng = wrdDoc.Range
        Rng.Collapse 0  'wdCollapseEnd
        Rng.PasteAndFormat (wdFormatPlainText)
        .Content.InsertParagraphAfter
        .Content.InsertAfter "Average Intensity-"
        With Sheets("Calculator")
            Set rngWord = .Range("R6")
        End With
        
        rngWord.Copy
        
        Set Rng = wrdDoc.Range
        Rng.Collapse 0  'wdCollapseEnd
        Rng.PasteAndFormat (wdFormatPlainText)
    End With
    
    With wrdDoc
          
         .SaveAs ("C:\Users\Eli\Desktop\Journal.doc")
           .Close ' close the document
        End With
        wrdApp.Quit ' close the Word application
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,863

    Re: Have to modify codes to bypass the clipboard

    instead of whole first this shall do:
    Sheets("Daily Calculator").Range("k2").End(xlToLeft).Offset(0, 1).resize(77,1).value = Range("T2:T78").value
    PS. Do you really want to go left from K2? (could be depending on spreadsheet design, but is less typical than right, so I'm asking).


    With Word - unfortunately I don't feel competent enough.
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Have to modify codes to bypass the clipboard

    Thank you. I had to add
    =("Calculator")
    and it worksFrom left or right works, but I changed it to go to the right so the code is more universal across multiple sheets. Some sheets only get about 2-3 columns filled before being cleared, some get hundreds of columns filled. I wasn't aware the resize portion of the code was what was preventing me from doing inserting multiple cells.

  4. #4
    Forum Contributor
    Join Date
    04-02-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Have to modify codes to bypass the clipboard

    With Sheets
        Application.CutCopyMode = False
    End With
    I inserted this at the end of my export to journal code and it worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Write macro to bypass clipboard
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2014, 08:53 AM
  2. Bypass Clipboard when copy/pasting non-contiguous rows
    By mlj61289 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2014, 03:11 PM
  3. [SOLVED] Is it possible to bypass the pw to modify all wb
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-15-2013, 04:02 PM
  4. [SOLVED] clipboard bypass not working
    By rossg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2013, 06:01 PM
  5. bypass clipboard too large comment
    By tdyl1969 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-18-2009, 09:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1