Moving data to another cell & Save help please

biznez · Dec 11, 2009

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

rconverse · Dec 11, 2009

Here's a macro that will move the data for you.

Code:

Sub Macro1()

i = 1

Do Until i = 3

    If i = 1 Then
        strLetter = "B"
        colLetter = "AA"
    End If
    
    If i = 2 Then
        strLetter = "C"
        colLetter = "AB"
    End If
    
    lnglastrow = Sheets("Sheet1").Range("Z65535").End(xlUp).Row
    
    Set r = Range("Z2:Z" & lnglastrow)
    
    firstRow = 2
    
    For Each c In r
        
        If c.Value = strLetter Then
               
            Range(colLetter & firstRow).Value = strLetter
            
            firstRow = firstRow + 1
            
        End If
        
    
    Next

    i = i + 1

Loop

Columns("Z:Z").Replace What:="B", Replacement:=""
Columns("Z:Z").Replace What:="C", Replacement:=""
Columns("Z:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Range("Z1").Select

End Sub

Before Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

3

A

4

B

5

B

6

C

7

C

8

A

9

C

10

B

11

B

12

C

13

C

14

C

15

A

16

A

17

B

18

B

19

A

20

C

21

A

22

B

Sheet1

After Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

B

C

3

A

B

C

4

A

B

C

5

A

B

C

6

A

B

C

7

A

B

C

8

A

B

C

Sheet1

HTH,
Roger

rconverse · Dec 11, 2009

You should be able to use something like this for saving.

Code:

ActiveWorkbook.SaveAs "C:\Documents and Settings\rconverse\My Documents\VBA Links\Excel\" & Format(Date, "mmm") & " (" & _
Format(Date, "dd") & ") DB (" & lnglastrow - 1 & ").xls"

HTH,
Roger

biznez · Dec 11, 2009

rconverse said:
Here's a macro that will move the data for you.

Code:

Sub Macro1() i = 1 Do Until i = 3 If i = 1 Then strLetter = "B" colLetter = "AA" End If If i = 2 Then strLetter = "C" colLetter = "AB" End If lnglastrow = Sheets("Sheet1").Range("Z65535").End(xlUp).Row Set r = Range("Z2:Z" & lnglastrow) firstRow = 2 For Each c In r If c.Value = strLetter Then Range(colLetter & firstRow).Value = strLetter firstRow = firstRow + 1 End If Next i = i + 1 Loop Columns("Z:Z").Replace What:="B", Replacement:="" Columns("Z:Z").Replace What:="C", Replacement:="" Columns("Z:Z").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp Range("Z1").Select End Sub

Before Macro:

Excel tables to the web >> Excel Jeanie HTML 4

hi rconverse, thanks for the code but it doesn't seem to be working properly
only A's work but B's and C's just disappear and i want it to show like the table below

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; width: 252px; height: 180px;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>Z</td><td>AA</td><td>AB</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>A</td><td>
</td><td>
</td></tr><tr><td style="vertical-align: top;">9
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">10
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">11
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">12
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">13
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">14
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">15
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">16
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">17
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">18
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">19
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr></tbody></table>

rconverse · Dec 11, 2009

Code:

Sub Macro4()
'
' 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
    
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

Before Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

3

B

4

C

5

A

6

B

7

C

8

A

9

B

10

C

11

A

12

B

13

C

14

A

15

B

16

C

17

A

18

B

19

C

20

A

21

B

22

C

23

A

24

B

25

C

26

A

27

B

28

C

29

A

30

B

31

C

Sheet1

After Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

3

A

4

A

5

A

6

A

7

A

8

A

9

A

10

A

11

A

12

B

13

B

14

B

15

B

16

B

17

B

18

B

19

B

20

B

21

B

22

C

23

C

24

C

25

C

26

C

27

C

28

C

29

C

30

C

31

C

Sheet1

HTH,
Roger

biznez · Dec 11, 2009

rconverse said:

Code:

Sub Macro4()
'
' 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
    
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

Before Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

3

B

4

C

5

A

6

B

7

C

8

A

9

B

10

C

11

A

12

B

13

C

14

A

15

B

16

C

17

A

18

B

19

C

20

A

21

B

22

C

23

A

24

B

25

C

26

A

27

B

28

C

29

A

30

B

31

C

Sheet1

After Macro:

Excel Workbook

Z

AA

AB

1

A

B

C

2

A

3

A

4

A

5

A

6

A

7

A

8

A

9

A

10

A

11

A

12

B

13

B

14

B

15

B

16

B

17

B

18

B

19

B

20

B

21

B

22

C

23

C

24

C

25

C

26

C

27

C

28

C

29

C

30

C

31

C

Sheet1

HTH,
Roger

Hi there rconverse
when i run this macro, the data in column Z gets deleted and also it highlights column Z which i don't want

rconverse · Dec 11, 2009

In this post you show all A's in Z, B's in AA, and C's in AB. Now you want the original values to stay in column Z?

biznez said:
rconverse said:

hi rconverse, thanks for the code but it doesn't seem to be working properly
only A's work but B's and C's just disappear and i want it to show like the table below

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; width: 252px; height: 180px;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>Z</td><td>AA</td><td>AB</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>A</td><td>
</td><td>
</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td>A</td><td>
</td><td>
</td></tr><tr><td style="vertical-align: top;">9
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">10
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">11
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">12
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">13
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">14
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">B
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">15
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">16
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">17
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">18
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr><tr><td style="vertical-align: top;">19
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">C
</td></tr></tbody></table>

Click to expand...

biznez said:
Hi there rconverse
when i run this macro, the data in column Z gets deleted (see above) and also it highlights column Z which i don't want (see below).

You can just insert this line and have the last referenced cell be whatever you want so column Z is not highlighed.

Code:

Range("A1").Select

HTH,
Roger

biznez · Dec 11, 2009

rconverse said:
In this post you show all A's in Z, B's in AA, and C's in AB. Now you want the original values to stay in column Z?

You can just insert this line and have the last referenced cell be whatever you want so column Z is not highlighed.

Code:

Range("A1").Select

HTH,
Roger

no, B'c and C's is working fine, just the A's dont show up at all, this code is highlighted in yellow for some reason

Selection.Find(What:="b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

biznez · Dec 11, 2009

rconverse said:
In this post you show all A's in Z, B's in AA, and C's in AB. Now you want the original values to stay in column Z?

You can just insert this line and have the last referenced cell be whatever you want so column Z is not highlighed.

Code:

Range("A1").Select

HTH,
Roger

here is the code i am using

Code:

Sub Button2_Click()
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:AB5000")
Range("AB2:AB5000").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
Range("Z1").Select
End Sub

rconverse · Dec 11, 2009

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

Code:

' 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

Moving data to another cell & Save help please

biznez

Board Regular

Excel Facts

rconverse

Well-known Member

rconverse

Well-known Member

biznez

Board Regular

rconverse

Well-known Member

biznez

Board Regular

rconverse

Well-known Member

biznez

Board Regular

biznez

Board Regular

rconverse

Well-known Member

Similar threads

Forum statistics

Share this page

Moving data to another cell & Save help please

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock