VBA Selective Copy?

Question

I'm looking to copy certain column cells one row at a time (i.e. A2, C2, G2 then onto A3, C3, G2) pasted onto a new sheet. I've looked at using:

Sheets("Sheet1").Range("A1:A10").Copy

But I need to copy data from certain columns.

Some Pseudo Code:

Iterate through each row
If the date in column A confines to the conditional statement, copy pre-determined column values and copy to new sheet.
Check the next row.

I currently have a for loop iterating from rows 1 to 50.

'Copy Titles
    Sheet2.Range("A1, E1, J1, K1, W1:X1, Y1, AA1").Copy
    Sheets.Add
    ActiveSheet.Range("A1").PasteSpecial
'Copy Data   
'If today's date is later than a date in the Column A, copy the cell over.
?


'Fix Column Width
    Range("A1:H1").ColumnWidth = 20
'Align Columns
    Columns("A:H").HorizontalAlignment = xlCenter

End Sub

I'm thinking under each selection, I would do the same statement but copy onto the new sheet instead? Thank you in advance!

Ahhh - I can't recommend **not** using `.Select` more. I highly suggest reading through [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and apply that to your macro. — BruceWayne, Jun 10 '16 at 15:40
Ah okay - but where's the rest? You say you loop, but I don't see a loop. And so you're copying those values from `Sheet2`, and pasting these values into a new sheet. There's a date in column A (where?) that you want to check, and if so, copy that to ...the new sheet? — BruceWayne, Jun 10 '16 at 16:16
@BruceWayne, I've decided to take out the loop for now, for simplicity. Yes, the date would be one of the cell values that I would copy. So the process would be like: If the date in column A is before today's date, copy the date and all other cell data to the new sheet. I hope that makes sense. — rmdlp, Jun 13 '16 at 13:57

score 2 · Answer 1 · answered Jun 10 '16 at 12:44

2

You can select the ranges in any order you want.

For example, you could use:

Range("A1:C1,E1,G1").Select

to select columns A-C, E and G

answered Jun 10 '16 at 12:44

Takarii

1,612
2
18
29

score 1 · Answer 2 · answered Jun 10 '16 at 13:25

1

Here is one cell at a time:

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value

Here is one row at a time:

Sheets("Sheet1").Rows("1:1").Copy Destination:=Sheets("Sheet2").Rows("1:1")

answered Jun 10 '16 at 13:25

Murgy

25
1
6

Not quite what I'm looking for, but I updated the question! – rmdlp Jun 10 '16 at 13:44

score 1 · Answer 3 · answered Jun 13 '16 at 15:54

Your request and the code you have don't really match, so I'm taking a best guess as to what your main idea is. This won't (likely) solve the problem, but hopefully can show you how you can go about this.

Read through the following, see if it's similar to what you're looking for:

Sub move_Info()
' This sub will look in each cell in column A, and if the cell's date is BEFORE today's date,
' then copy the cell, to a new row in a new sheet, same column.

Dim cel As Range, rng As Range
Dim iDate$, tDay As Date
Dim columnsToCheck() As Variant
Dim dataWS As Worksheet, newWS As Worksheet

Set dataWS = Sheets("Sheet1") ' Change this as needed. This is where your data is.
Set newWS = Sheets.Add(after:=dataWS)
newWS.Name = "Copied Info"

columnsToCheck() = Array(1, 2) ' this will check Columns A and B. To add more, just add more numbers, i.e.
'columnToCheck() = Array(1,2,3,5)

tDay = Date
Debug.Print "Today is " & tDay

Dim i&, lastRow&, nextRow&
nextRow = 1

dataWS.Activate

With dataWS
    For i = LBound(columnsToCheck) To UBound(columnsToCheck)
        lastRow = .Cells(.Rows.Count, columnsToCheck(i)).End(xlUp).Row
        Set rng = .Range(.Cells(1, columnsToCheck(i)), .Cells(lastRow, columnsToCheck(i)))

        For Each cel In rng
            If cel.Value < tDay Then
                cel.Copy
                newWS.Range(newWS.Cells(nextRow, columnsToCheck(i)), newWS.Cells(nextRow, columnsToCheck(i))).PasteSpecial
                nextRow = newWS.Cells(newWS.Rows.Count, columnsToCheck(i)).End(xlUp).Row + 1
                Application.CutCopyMode = False
            End If
        Next cel

        'Reset nextRow, since you're starting in a new column
        nextRow = 1
    Next i
End With

End Sub

You can step through it with F8, to watch how it works one line at a time.

Thank you for your response! This is very close to what I need. I would only need that first column as the conditional. So hopefully along the lines of: Check if the date is not equal to today's date, then copy cells in that same row onto the new sheet (i.e. AX, FX, QX where X is an integer.) — rmdlp, Jun 13 '16 at 20:29
@rmdlp - So, you need to check **only** column A for the date, if the date matches (is before to `TODAY`), copy that whole row to the next sheet? I think I'm missing some part, because you reference columns F and Q. — BruceWayne, Jun 13 '16 at 20:46
I don't need to copy the entire row. Only predetermined columns would need to be copied. So for a little insight, my spreadsheet has information that I don't need. So I would need to pick and choose the data that I want to extract once I find out that the date satisfies the conditional. — rmdlp, Jun 14 '16 at 12:39

VBA Selective Copy?

3 Answers3