(Noob Q) How do I get my code to paste to a different column?

[mrjimmyvo]

So the code I have runs good, but only has one problem... I cannot get it to paste to anywhere besides column "A" I have tried changing the column letter, but I get an error code saying the "copy/paste cell are not the same size".
Any help would be appreciated!


<code>Sub HEA_Filter_Names()
Dim strArray As Variant
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim NoRows As Long
Dim DestNoRows As Long
Dim I As Long
Dim J As Integer
Dim rngCells As Range
Dim rngFind As Range
Dim Found As Boolean

strArray = Array("ack-")

Set wsSource = ActiveSheet

NoRows = wsSource.Range("A65536").End(xlUp).Row
DestNoRows = 1
Set wsDest = Sheets("Real Alarms")

For I = 1 To NoRows

Set rngCells = wsSource.Range("B" & I)
Found = False
For J = 0 To UBound(strArray)
Found = Found Or Not (rngCells.Find(strArray(J)) Is Nothing)
Next J

If Found Then
rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)

DestNoRows = DestNoRows + 1
End If
Next I
End Sub</code>

 

[Peter_SSs]

You are copying an entire row: 16,384 cells.
If you paste them starting in column A of another row they will fit on the sheet.
If you try to paste them stating in, say, column C then there is only 16,382 cells in that row starting at column C and therefore the 16,384 cells will not fit, hence the error.
Most likely, you don't need to copy/paste the entire row?

 

[mrjimmyvo]

You are copying an entire row: 16,384 cells.
If you paste them starting in column A of another row they will fit on the sheet.
If you try to paste them stating in, say, column C then there is only 16,382 cells in that row starting at column C and therefore the 16,384 cells will not fit, hence the error.
Most likely, you don't need to copy/paste the entire row?

Hey how do I fix the problem? Which part of the code do I change? I tried changing A65536, but it did not work.

 

[mrjimmyvo]

Hey how do I fix the problem? Which part of the code do I change? I tried changing A65536, but it did not work.

I want to change it to column F.

 

[Peter_SSs]

I want to change it to column F.

You haven't said what you want copied to column F.
It appears that your code is searching for something in column B. If you find what you are looking for in, say, B8 what cell(s) from row 8 do you want pasted starting at column F? (For example, it might be A8:D8 )
Remember we cannot see your data or know exactly what you are trying to do so you need to explain clearly.

 

[mrjimmyvo]

You haven't said what you want copied to column F.
It appears that your code is searching for something in column B. If you find what you are looking for in, say, B8 what cell(s) from row 8 do you want pasted starting at column F? (For example, it might be A8:D8 )
Remember we cannot see your data or know exactly what you are trying to do so you need to explain clearly.

Basically my code is searching for "ACK-" in column B. If it is found, the entire row will be copied and pasted to the next sheet.

I am having an error because there are too many cells to be copy/paste into a different column. So how do I set a range for what is copied in the row, or even better how do I only copy cells with data?

I think I have to edit this line... :

<code>If Found Then
rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)

DestNoRows = DesNoRows + 1

Thanks!</code>

 

[Peter_SSs]

.. how do I only copy cells with data?

Do we know which columns have, or might have, data?
That is, would it be safe to say that data never extends beyond column Z or column BG or something?
Or should we build into the code to find out the extent of the data left to right?

 

[mrjimmyvo]

Do we know which columns have, or might have, data?
That is, would it be safe to say that data never extends beyond column Z or column BG or something?
Or should we build into the code to find out the extent of the data left to right?

Yeah it wont extend pass Z

 

[Peter_SSs]

Yeah it wont extend pass Z

In that case, try making this change in your code

<del>rngCells.EntireRow.Copy wsDest.Range("A" & DestNoRows)</del>
rngCells.EntireRow.Resize(,26).Copy wsDest.Range("F" & DestNoRows)