3

What I am trying to do is, in each worksheet:
1. Copy all numbers in column G (G23 and down) and paste special at the end of column A.
2. Format the pasted numbers to have only one decimal point.
3. Copy the pasted numbers and paste them at the end of column B, and remove duplicates.

I finished Step 1, but I don't know how to do Step 2 and 3.... I could not find ways to select the just pasted numbers at the bottom of Column A. I am new to VBA - Many thanks for your help.

Here is the code I have so far:

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim rng As Range
Dim last As Long

'Set WS_Count equal to the number of worksheets in the active workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

'Begin loop, starts from the sixth worksheet
For I = 6 To WS_Count

    last = Worksheets(I).Cells(Rows.Count, "G").End(xlUp).Row
    Set rng = Worksheets(I).Range("G23:G" & last)

    Worksheets(I).Select
        rng.Copy
        Worksheets(I).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteFormulasAndNumberFormats

    ....(what should I do next here?)

Next I

Application.ScreenUpdating = True

End Sub
Grinsummer
  • 109
  • 1
  • 12
  • I'd try recording a macro following the steps 2 and 3 and edit the code – tdmsoares Dec 21 '15 at 23:39
  • Thanks, but the problem with macro is that I can't repeat it for all the worksheets. – Grinsummer Dec 21 '15 at 23:42
  • After recording this macro, click Edit, then will show the code. Then you can adapt it to your vba – tdmsoares Dec 21 '15 at 23:44
  • Yes sorry I misunderstood you. I just tried that. It does not work because in the macro VBA code, it just records the specific cell that I copied the values into; e.g., it recorded that I copied and pasted the values to cell A255 and down. I would not be able to duplicate this process for other worksheets, because the numbers of rows of data are different across worksheets. – Grinsummer Dec 21 '15 at 23:51
  • To get the cells after copying it, you can use `rng.Cells.Count` to get the amount of cells. Then go for the range of the copy start (do not directly paste them, put the range to paste into a new variable like `rng2`). Now you simply use `Range(rng2, rng2.Offset(rng.Cells.Count - 1))` and you get the whole range of your pasted cells... (just avoiding `selection` which could be used as a short trip) – Dirk Reichel Dec 22 '15 at 00:28
  • Hi Dirk - may I know how to "put the range to paste into a new variable like rng2"? Can you give an example code please? – Grinsummer Dec 22 '15 at 00:36

1 Answers1

2

You already know the range of the values to be pasted. The first cell of that range is the one used for PasteSpecial and the number of rows will be equal to the number of rows in rng. The required information is already there, all that is needed is to set it to a variable.

Here's a snippet to illustrate:

Sub Example()
    Dim last As Long
    Dim copyRange As Range
    Dim pasteRange As Range

    last = Worksheets(1).Cells(Rows.Count, "G").End(xlUp).Row
    Set copyRange = Worksheets(1).Range("G23:G" & last)
    Set pasteRange = Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2) _
                    .Resize(copyRange.Cells.Count, 1)

    copyRange.Copy
    pasteRange.PasteSpecial xlPasteFormulasAndNumberFormats

    ' use pasteRange for modifying the pasted data

End Sub

Changing the format can be done with range.NumberFormat. In this case you could do:

pasteRange.NumberFormat = "0.0"

I find it rather strange that you first copy the NumberFormat and then change it, though! Maybe you could choose to format the entire column A and not paste the formatting?

range.RemoveDuplicates is built in Excel for the third step. Here are a couple of answers that showcase how it can be used:

Community
  • 1
  • 1
natancodes
  • 998
  • 8
  • 12
  • Thanks - This works perfectly for Step 2! However, how do I do Step 3 - paste the same thing to the end of column B? I tried setting up another pasterange: Set pasteRange2 = Worksheets(I).Cells(Rows.Count, 2).End(xlUp)(2).Resize(rng.Cells.Count, 2), and then: pasteRange2.PasteSpecial xlPasteValuesAndNumbersFormats. But it does not work. – Grinsummer Dec 22 '15 at 01:23
  • You could avoid having to deal with two paste ranges by switching the copy range with `Set copyRange = pasteRange` and then copy-pasting just as you did before. Then it's just a matter of finding all values from column B and using that range for `RemoveDuplicates` - which is really just about the same thing you did to build the initial copyRange `rng`. – natancodes Dec 22 '15 at 02:06
  • The problem with the paste code in the comment looks to be the `Resize(rows, columns)` call, where you passed 2 as the column count. This will throw an error as the copy range and paste range *must have identical sizes*. – natancodes Dec 22 '15 at 02:08
  • Thank you J_V! This is very helpful. – Grinsummer Dec 22 '15 at 17:30