Moving data to another cell & Save help please

[biznez]

I have 3 columns Z, AA, AB
The heading for Z1 is “A”, AA1 is “B” and AB1 is “C”
In column Z2:Z2000, there is a mix of A, B’s and C’s
I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button

Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls”
The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this

Thanks a lot everyone and enjoy your holidays

 

[biznez]

You are missing the sort portion. You have to sort by the code (A, B, C) for the macro to work properly.

' Macro4 Macro
'

'
'sort all data by the row with the code

Rows("1:49").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("N1:AB49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

when i use the sort portion, i get this error message for this line

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
"Run-time error'438':
"Object doesnt support this property or method"

 

[rconverse]

You don't need these two lines. I didn't delete them earlier.

Rows("1:49").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

Make sure you have the correct range for the sort.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("N1:AB49")        
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

 

[biznez]

You don't need these two lines. I didn't delete them earlier.

Rows("1:49").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

Make sure you have the correct range for the sort.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("N1:AB49")        
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

Oh man, this is giving me a headache now hehe...i deleted the first two lines and now i get an error message for the next code
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[biznez]

Oh man, this is giving me a headache now hehe...i deleted the first two lines and now i get an error message for the next code
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

do u want to double check this for me please

Sub Button2_Click()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Z1:Z49") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("N1:AB49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
    
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""B"", RC[-1], """")"
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""C"", RC[-2], """")"
Range("AA2:AB2").Select
Selection.AutoFill Destination:=Range("AA2:AB49")
Range("AB2:AB49").Select
ActiveWindow.SmallScroll Down:=-12
Columns("Z:AB").Copy
Range("Z1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("Z:Z").Select
    Selection.Find(What:="b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub