Results 1 to 6 of 6

Using vba array to copy data and paste to another workbook

Threaded View

  1. #1
    Registered User
    Join Date
    08-15-2021
    Location
    Saudi Arabia
    MS-Off Ver
    365 (Windows)
    Posts
    35

    Using vba array to copy data and paste to another workbook

    Hello Guys

    Is it possible to convert below code to array?
    I believe array is way faster than the code that i have provided.

    Thanks in Advance.

    Regards
    Zelda

    Option Explicit
    
    Sub ImportData()
    
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    Dim lCopyLastRow As Long
    Dim lDestLastRow As Long
    Dim iDirectory As String
    Dim iFileName As String
    
      Application.ScreenUpdating = False
        iDirectory = "D:\Import\"
        iFileName = Dir(iDirectory & "DELIVERY.XLSX")
        Workbooks.Open (iDirectory & iFileName)
        
        Set wsCopy = Workbooks("DELIVERY.XLSX").Worksheets(1)
        Set wsDest = ThisWorkbook.Worksheets("SAMPLE")
        
        
        lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
        lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "c").End(xlUp).Offset(1).Row
        wsCopy.Range("A2:P" & lCopyLastRow).Copy _
        wsDest.Range("C" & lDestLastRow)
         
        Workbooks(iFileName).Close
        iFileName = Dir()
    
            Columns(3).Select
            With Selection
                .NumberFormat = "General"
                .Value = .Value
            End With
            Columns(4).Select
            With Selection
                .NumberFormat = "dd-mmm-yy;@"
                .Value = .Value
            End With
            Columns(7).Select
            With Selection
                .NumberFormat = "General"
                .Value = .Value
            End With
            Columns(18).Select
            With Selection
                .NumberFormat = "General"
                .Value = .Value
            End With
     
       Application.ScreenUpdating = True
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-26-2023, 05:56 PM
  2. Replies: 3
    Last Post: 02-16-2018, 06:40 PM
  3. [SOLVED] Macro to find data in source workbook and copy paste to target workbook
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-23-2014, 06:21 AM
  4. Replies: 6
    Last Post: 03-26-2014, 11:40 PM
  5. [SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.
    By paullie1912 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2014, 02:38 AM
  6. Replies: 6
    Last Post: 01-29-2013, 07:01 AM
  7. select variables ranges, copy to array, paste the array in new workbook
    By Mathew in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 05:06 AM

Tags for this Thread

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