Solved

VBA Paste Column in Non-Sequential Order

Posted on 2014-10-14
2
630 Views
Last Modified: 2014-10-14
Hi Experts,

Looking for a little assistance,  I need to copy over some columns the range is A7:C70, F7:I70,K7:K70,D7:D70,L7:L70, DE7:EZ70 in this order.  However when I run the vba it copies the data over in sequential order.  How would I update the vba to place in the order specified?

Here is the macro
Sub BCopySummaryData()
    Dim wb As Workbook
    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet
    
    Set fso = New FileSystemObject
    
  'Delete Current Data below header
    With Sheets("BSum")
            Rows("7:65536").Select
            Selection.Delete
            Range("A7").Select
        End With
    
    Application.AskToUpdateLinks = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    '--------------------------------------------------------------------------------
    'For test purposes, I am using the "My Documents" folder... this needs to change
    ' To use whatever folder you need
    '--------------------------------------------------------------------------------
    
    
    Set myFolder = fso.GetFolder("\\SSFilePrint\GROUPSHARE\Store Planning\LSP Shared")
    
        
    For Each myFile In myFolder.Files
        Select Case UCase(fso.GetExtensionName(myFile.Path))
            Case "XLS", "XLSM", "XLSB", "XLSX":
        
                If newestFile Is Nothing Then
                    Set newestFile = myFile
                ElseIf myFile.DateLastModified > newestFile.DateLastModified Then
                    Set newestFile = myFile
                End If
        End Select
    Next


   
    '--------------------------------------------------------------------------------
    'At this point... "newestFile" is a File object that is the newest Excel File in your folder
    ' The following code will open it, and now you have to copy from whatever range you need data from
    '--------------------------------------------------------------------------------
   
    
    If Not newestFile Is Nothing Then
        Application.Workbooks.Open newestFile.Path
        Set wb = Application.Workbooks(newestFile.Name)
    'or if you now the name of the sheet it could be something like : Set ws = wb.Sheets("Sheet1")
        Set ws = wb.Sheets("Summary")
        
     Set lastSourceCell = LastCell(ws)
        If lastSourceCell Is Nothing Then
            MsgBox "Nothing to copy - stopping"
            wb.Close
            Exit Sub
        End If
        
        Set lastDestCell = LastCell(ThisWorkbook.Sheets("BSum"))
        If lastDestCell Is Nothing Then
            destinationRow = 1
        Else
            destinationRow = lastDestCell.Row + 1
        End If
        
 
                ws.Range("A7:C70, F7:I70,K7:K70,D7:D70,L7:L70, DE7:EZ70").Copy
                ThisWorkbook.Sheets("BSum").Range("A" & destinationRow).PasteSpecial xlPasteValues
                ThisWorkbook.Sheets("BSum").Range("A" & destinationRow).PasteSpecial xlPasteFormats
                destinationRow = destinationRow + 1
        
        
      
        Application.ScreenUpdating = True
        Application.DisplayAlerts = False
        wb.Close
        MsgBox "Copy Complete"

    End If
    

'Add date ran

   Sheets("BSum").Range("F1").Value = Date
   Application.Calculation = xlCalculationAutomatic

    
     MsgBox "All Updates Complete"
   
End Sub

Open in new window

0
Comment
Question by:jmac001
2 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40380845
I presume the issue is with lines 72-74.  You will have to break up the copy and paste into six distinct sections if you want those copied ranges pasted in the listed order or in identical columns.  Using the Split function, one can create a loop through your source and destination ranges:

        Dim arrRanges, arrDestCol As Variant
        Dim x As Integer
        arrRanges = Split("A7:C70,F7:I70,K7:K70,D7:D70,L7:L70,DE7:EZ70", ",")
        arrDestCol = Split("A,D,H,I,J,K", ",")
        For x = 0 To UBound(arrRanges)
            ws.Range(arrRanges(x)).Copy
            ThisWorkbook.Sheets("BSum").Range(arrDestCol(x) & destinationrow).PasteSpecial xlPasteValues
            ThisWorkbook.Sheets("BSum").Range(arrDestCol(x) & destinationrow).PasteSpecial xlPasteFormats
        Next x
        destinationrow = destinationrow + 1

Open in new window


I couldn't test your code directly since the LastCell function was not included, but I presume it returns the last available cell to copy/paste data.

Regards,
-Glenn
0
 

Author Closing Comment

by:jmac001
ID: 40380999
Thank you Glenn actually working the way I envisioned and has all of the correct columns
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question