How do I specify # of characters in this array?

[thardin]

Right now this code works perfectly to copy columns 1 and 5 from one workbook to another. However, I need to modify it so it only copies the left 8 digits of column 1(eliminating the last 2 digits) . like =LEFT(A2,8)

How would I fix this?
Thank You.

    Dim Ary As Variant
    Dim Fname As String
    Dim lr As Long
   
   lr = Cells(Rows.Count, "A").End(xlUp).row
  
   Fname = "529 A Shares Restricted Purchases violations TD " & Format(Date, "mmddyy")
   Fname2 = "529ACANCEL" & Format(Date, "mmddyy")
   With Workbooks(Fname & ".xlsx").Sheets("Sheet1").UsedRange
      Ary = Application.Index(.Value, .Worksheet.Evaluate("row(2:" & .Rows.Count & ")"), Array(1, 5))
   End With
   Workbooks(Fname2 & ".csv").ActiveSheet.Range("A1").Resize(UBound(Ary), 2).Value = Ary
  
   ActiveSheet.UsedRange.EntireColumn.AutoFit
    ActiveSheet.UsedRange.EntireRow.AutoFit

 

[johnnyL]

Absolutely. If it doesn't pertain to this thread, I would suggest you start a new thread.

 

[thardin]

Absolutely. If it doesn't pertain to this thread, I would suggest you start a new thread.

Yeah, I already did

 

[thardin]

I think I am having an issue with this code all of a sudden. today I realized when I only have 1 item listed in my SourceFile (which is in row 2), that item is pasting twice in the destination file(in rows 2 AND 3).

How can this be and how do I fix this?

 

[johnnyL]

Sub TestV3()
'
    Dim ArraySize               As Long
    Dim DestinationDataStartRow As Long, SourceDataStartRow As Long
    Dim Cell                    As Range
    Dim FileExtention           As String
    Dim DestinationFileName     As String, SourceFileName   As String
    Dim ArrayA                  As Variant, ArrayE          As Variant
'
    DestinationFileName = "Book1"                                                                       ' <--- Set this to the DestinationFileName
    DestinationDataStartRow = 2                                                                         ' <--- Set this to the start row of destination data
    SourceFileName = "Book2"                                                                            ' <--- Set this to the SourceFileName
    FileExtention = ".xlsx"                                                                             ' <--- Set this to the FileExtention of the files
    SourceDataStartRow = 2                                                                              ' <--- Set this to the start row of the source data
'
    With Workbooks(SourceFileName & FileExtention).Sheets("Sheet1")                                     ' With SourceFileName sheet ...
        LastRowInColumnA = .Range("A" & .Rows.Count).End(xlUp).Row                                      '   Get last Row Number used of column A in source sheet
        
        ArrayA = .Range("A" & SourceDataStartRow & ":A" & LastRowInColumnA)                             '   Load column A of source data into 2D 1 based Ary. RC
        ArrayE = .Range("E" & SourceDataStartRow & ":E" & LastRowInColumnA)                             '   Load column E destination data into 2D 1 based Ary. RC
'
        ArraySize = WorksheetFunction.CountA(ArrayE)                                                    '   Get the count of elements in the array
    End With
'
    With Workbooks(DestinationFileName & FileExtention).ActiveSheet                                     ' With DestinationFileName sheet ...
        .Range("A" & DestinationDataStartRow & ":A" & DestinationDataStartRow + ArraySize - 1) = ArrayA '   Copy ArrayA to DestinationFileName column A
        .Range("B" & DestinationDataStartRow & ":B" & DestinationDataStartRow + ArraySize - 1) = ArrayE '   Copy ArrayE to DestinationFileName column B
'
        For Each Cell In .Range("A" & DestinationDataStartRow & ":A" & _
                .Range("A" & .Rows.Count).End(xlUp).Row)                                                '   Loop to loop through column A of DestinationFileName
            Cell.Formula = "=Left(" & Cell.Value & ", 8)"                                               '       Copy formula down the column A of DestinationFile
            Cell.Value = Cell.Value                                                                     '       Remove the formulas from the column, leave values
        Next                                                                                            '   Loop back
'
        .UsedRange.EntireColumn.AutoFit                                                                 ' Autofit columns in DestinationFileName data range
        .UsedRange.EntireRow.AutoFit                                                                    ' Autofit rows in DestinationFileName data range
    End With
End Sub

 

[thardin]

Sub TestV3()
'
    Dim ArraySize               As Long
    Dim DestinationDataStartRow As Long, SourceDataStartRow As Long
    Dim Cell                    As Range
    Dim FileExtention           As String
    Dim DestinationFileName     As String, SourceFileName   As String
    Dim ArrayA                  As Variant, ArrayE          As Variant
'
    DestinationFileName = "Book1"                                                                       ' <--- Set this to the DestinationFileName
    DestinationDataStartRow = 2                                                                         ' <--- Set this to the start row of destination data
    SourceFileName = "Book2"                                                                            ' <--- Set this to the SourceFileName
    FileExtention = ".xlsx"                                                                             ' <--- Set this to the FileExtention of the files
    SourceDataStartRow = 2                                                                              ' <--- Set this to the start row of the source data
'
    With Workbooks(SourceFileName & FileExtention).Sheets("Sheet1")                                     ' With SourceFileName sheet ...
        LastRowInColumnA = .Range("A" & .Rows.Count).End(xlUp).Row                                      '   Get last Row Number used of column A in source sheet
       
        ArrayA = .Range("A" & SourceDataStartRow & ":A" & LastRowInColumnA)                             '   Load column A of source data into 2D 1 based Ary. RC
        ArrayE = .Range("E" & SourceDataStartRow & ":E" & LastRowInColumnA)                             '   Load column E destination data into 2D 1 based Ary. RC
'
        ArraySize = WorksheetFunction.CountA(ArrayE)                                                    '   Get the count of elements in the array
    End With
'
    With Workbooks(DestinationFileName & FileExtention).ActiveSheet                                     ' With DestinationFileName sheet ...
        .Range("A" & DestinationDataStartRow & ":A" & DestinationDataStartRow + ArraySize - 1) = ArrayA '   Copy ArrayA to DestinationFileName column A
        .Range("B" & DestinationDataStartRow & ":B" & DestinationDataStartRow + ArraySize - 1) = ArrayE '   Copy ArrayE to DestinationFileName column B
'
        For Each Cell In .Range("A" & DestinationDataStartRow & ":A" & _
                .Range("A" & .Rows.Count).End(xlUp).Row)                                                '   Loop to loop through column A of DestinationFileName
            Cell.Formula = "=Left(" & Cell.Value & ", 8)"                                               '       Copy formula down the column A of DestinationFile
            Cell.Value = Cell.Value                                                                     '       Remove the formulas from the column, leave values
        Next                                                                                            '   Loop back
'
        .UsedRange.EntireColumn.AutoFit                                                                 ' Autofit columns in DestinationFileName data range
        .UsedRange.EntireRow.AutoFit                                                                    ' Autofit rows in DestinationFileName data range
    End With
End Sub

That Worked Better today!
Thanks

 

[johnnyL]

Glad to help @thardin.