+ Reply to Thread
Results 1 to 15 of 15

Copy File Macro - Prevent from seeing "Chunking Along"

Hybrid View

  1. #1
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Copy File Macro - Prevent from seeing "Chunking Along"

    Why are you opening/closing the destination workbook every time you want to copy a row to it?

    Open it once, then run through the source data copying what's required and then close it.

    Try this.
    Sub CopyToBackupFile()
    Dim wbDst As Workbook
    Dim wsDst As Worksheet
    Dim wsSrc As Worksheet
    Dim LastRow As Long, i As Long
    
        Set wsSrc = ActiveSheet
    
        LastRow = wsSrc.Range("A" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        
        Set wbDst = Workbooks.Open(Filename:="C:\Users\My\Desktop\Backup.xlsx")
        Set wsDst = wbDst.Worksheets("Sheet1").Select
    
        For i = 4 To LastRow
    
            If wsSrc.Cells(i, 1) <> "" Then
                wsSrc.Cells(i, 1).Resize(, 16).Copy wsDst.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            End If
    
        Next i
    
        wbDst.Close SaveChanges:=True
    
        Application.ScreenUpdating = True
        
    End Sub
    If posting code please use code tags, see here.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,432

    Re: Copy File Macro - Prevent from seeing "Chunking Along"

    Is there a way to copy and paste the formatting of the cells being copied as well ?

    I've tried (in my code):

    With Sheet1.Cels
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteFormats
            .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    End With

+ 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. Short "Basic" Macro to copy and paste formulas "N" times.
    By gradyhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 02:34 PM
  2. Need Macro code to attach the words "FILE COPY" to second page only when printed?
    By ElmerFud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2014, 12:03 PM
  3. Need Macro code to attach the words "FILE COPY" to second page only when printed?
    By ElmerFud in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-04-2014, 10:21 AM
  4. [SOLVED] VBA macro to copy ActiveSheet plus sheet named "Filters" to a separate file
    By CherylC in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2014, 08:19 PM
  5. [SOLVED] Copy contents from "Child" file to a "Master"
    By pipoliveira in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 02-13-2014, 09:44 AM
  6. [SOLVED] Prevent "copy to next empty row" from copying the Web Query link
    By 4evra in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2013, 09:10 AM
  7. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 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