VBA code don't copy all the data in a column to another column, only partial data in Mac OS

[Zem32619]

Hi Excel Masters.

Am doing a macro that should copy all data in a column to another column but it only copies partial data. The code works on Windows perfectly, but when using Mac, it only copies partial data. Can anyone pls extend some help, would be very much appreciated. Been stuck here for a day now. Thank you so much in advance.

Sheets("Sheet1").Select
Cells.Select
Selection.copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
'Range("L2:L" & lr).copy Destination:=Range("K2")
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("L:O").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Columns("V:V").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select

 

[Alex Blakenburg]

Can you describe what you are trying to achieve.
Currrently the macro is:

1. creating a new workbook with a copy of sheet 1
2. it is copying column L to column K twice
   1. The first time it is copied as values only
   2. The second time is is a straight copy totally defeats the purpose of the values only copy.
      Do you want this as value only or a full copy ?
3. Deleting column L:O
4. Deleting column V (is this definitely the right column since the original V will have moved due to deleting L:O)

If you can clarify item 2, I can give you some cleaned up to code to try to see if it fixes the issue.

 

[Zem32619]

Can you describe what you are trying to achieve.
Currrently the macro is:

1. creating a new workbook with a copy of sheet 1
2. it is copying column L to column K twice
   1. The first time it is copied as values only
   2. The second time is is a straight copy totally defeats the purpose of the values only copy.
      Do you want this as value only or a full copy ?
3. Deleting column L:O
4. Deleting column V (is this definitely the right column since the original V will have moved due to deleting L:O)

If you can clarify item 2, I can give you some cleaned up to code to try to see if it fixes the issue.

Hi Alex.

Yes, I want to copy all the data in column L to column K as value only. Currently, it copies only partial data (as values) in column L to K. Thanks.

 

[Alex Blakenburg]

Try this:-

Sub testTrimmed()

    Dim newWB As Workbook
    Dim newSht As Worksheet
    Dim lastRow As Long

    ' Copy whole of Sheet1 to a new workbook
    ActiveSheet.Copy
    Set newWB = ActiveWorkbook
    Set newSht = ActiveSheet    
    
    ' in New workbook
    With newSht
        lastRow = .Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        ' Copy L2 to Last Row to Column K values only
        .Range("L2:L" & lastRow).Copy
        .Range("K2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        
        'Delete column L:O
        .Columns("L:O").Delete
    
        'Deleted Column V
        .Columns("V:V").Delete
    
        .Range("A1").Select
    End With

End Sub