+ Reply to Thread
Results 1 to 4 of 4

Make Copy code easier

Hybrid View

Shimazu Make Copy code easier 03-25-2014, 05:47 AM
ragulduy Re: Make Copy code easier 03-25-2014, 05:49 AM
Shimazu Re: Make Copy code easier 03-25-2014, 05:53 AM
ragulduy Re: Make Copy code easier 03-25-2014, 06:01 AM
  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    52

    Question Make Copy code easier

    Hallo,
    I use a Line that will copy information frome one sheet to another.
    But there is Little delay in it ...can that be the vault of the code??
    is there easy way to make my code easy or compacter?

        'Factuurnummer/invoice Number Check'
            Sheets("Export Freshbooks").Range("B2:B1000").Copy Destination:=Sheets("Output").Range("B2")
            
    
        'FactuurDatum/Invoice Date Check'
            Sheets("Export Freshbooks").Range("C2:C1250").Copy Destination:=Sheets("Output").Range("C2")
            
            
            Application.ScreenUpdating = False
                 Sheets("Export Freshbooks").Range("C:C").NumberFormat = "dd/mm/yyyy;@"
                 Sheets("Output").Range("C:C").Value = Range("C:C").Value
            Application.ScreenUpdating = True
    
    
        'Items Check'
            Sheets("Export Freshbooks").Range("G2:G1250").Copy Destination:=Sheets("Output").Range("E2")
    
        'Items Uitleg omschrijving Check'
            Sheets("Export Freshbooks").Range("H2:H1250").Copy Destination:=Sheets("Output").Range("D2")
        
        
        'Line cost/ Factuurbedrag INCL>BTW'
             With Sheets("Output")
                 Sheets("Export Freshbooks").Range("N2:N1250").Copy
                 Sheets("Output").Range("G2").PasteSpecial (xlPasteValues)
             End With
        
            
        'Tax1 BTW Bedrag Check'
            With Sheets("Output")
                 Sheets("Export Freshbooks").Range("L2:L1250").Copy
                 Sheets("Output").Range("I2:I1250").PasteSpecial (xlPasteValues)
            End With
            
            
        'Grootboekrekening Gelinkt op ITem Check'
        
            With Sheets("Output")
                Range("J2:J1250").Formula = "=if(ISERROR(VLOOKUP(E2,Grootboekrekeningen!A:C,2)),"""",VLOOKUP(E2,Grootboekrekeningen!A:C,2))"
            End With
            
            
        
        'Debiteuren Check'
        
            With Sheets("Output")
                .Range("A2:A" & .Cells(Rows.Count, "B").End(xlUp).Row).Formula = _
                "=IFERROR(VLOOKUP('Export Freshbooks'!A2,Debiteuren!A:B,2,0),"""")"
            End With

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Make Copy code easier

    Instead of:
     With Sheets("Output")
                 Sheets("Export Freshbooks").Range("N2:N1250").Copy
                 Sheets("Output").Range("G2").PasteSpecial (xlPasteValues)
             End With
    This is usually quicker
    Sheets("Output").Range("G2:G1250").Value = Sheets("Export Freshbooks").Range("N2:N1250").Value

  3. #3
    Registered User
    Join Date
    02-17-2014
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    52

    Re: Make Copy code easier

    Okey thank you.. i can use that now for more of my lines

    is there a way to set a rang with out the number.... Example. .Range("G2:G") that it wil go on?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Make Copy code easier

    You would normally do something like:
    .Range("G2:G" & Range("G" & rows.count).end(xlup).row)
    to define a range that goes to the last used cell in a column.

+ 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] How can I make this code shorter by using a loop. or there's any easier shorcuts code
    By romarkevinruiz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-10-2014, 08:53 AM
  2. Replies: 5
    Last Post: 08-01-2013, 04:30 AM
  3. Please make it easier to access the IS functions (e.g., isblank) .
    By Blue Hornet in forum Excel Formulas & Functions
    Replies: 40
    Last Post: 09-06-2005, 07:05 PM
  4. [SOLVED] Need a formula that would make life easier
    By frustrated in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-29-2005, 12:05 PM
  5. [SOLVED] Need a formula that would make life easier
    By Frustrated in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2005, 10:05 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