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 and apply that to your macro. — BruceWayne
– BruceWayne, Commented Jun 10, 2016 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
– BruceWayne, Commented Jun 10, 2016 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
– rmdlp, Commented Jun 13, 2016 at 13:57

Takarii · Accepted Answer · 2016-06-10 12:44:50Z

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

Murgy · Accepted Answer · 2016-06-10 13:25:58Z

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")

BruceWayne · Accepted Answer · 2016-06-13 15:54:59Z

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 - 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.
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.

Collectives™ on Stack Overflow

VBA Selective Copy?

3 Answers 3

Comments

1 Comment

4 Comments

Linked

Hot Network Questions