Copy paste entire row different sheet column C

[nahge123]

I am trying to copy and paste to a different sheet, and need to paste the rows starting in column C rather than Column A.

THe VBA is for a Copy Unique rows. Works great but need to post in Column C of "Check List" rather than Column A. Don't need to copy the entire row, just out to Column BB. New to VBA.

Option Explicit
Sub CopyAndRemoveDups()
Dim cl As Range
Dim ws As Worksheet
Dim x As Long
Dim LastRow As Long
Dim rng As Range
Dim rngToDelete As Range


Set ws = Worksheets("query")
With ws

For Each cl In .Range("A1:A" & .Range("A65536").End(xlUp).Row)
If cl.Value <> "" Then
cl.EntireRow.Copy Worksheets("Check List").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next cl
End With

Set ws = Worksheets("Check List")
'Advanced Filter requires a header row- let's add a temporary one
ws.Rows(1).Insert
ws.Cells(1, 1).Value = "temp header"
'Set Filter column
Set rng = ws.Range("D1:D10000")
rng.AdvancedFilter xlFilterInPlace, Unique:=True
Set rngToDelete = rng.SpecialCells(xlCellTypeVisible)
ws.ShowAllData
rngToDelete.EntireRow.Hidden = True
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
rngToDelete.EntireRow.Hidden = False

'remove the temporary row
ws.Rows(1).Delete


End Sub


Thank you for your help

[royUK]

You need to use Code Tags in your post.

You cannot copy an entire row & start in C, the sizes will not match

[Rob*]

Do I read right, if I understand it as if you want to copy the row from A to BB in sheet "query" if there's any value in the cell in column A?
The paste in into sheet "Check List" from column C to BD?

[JOHN H. DAVIS]

Maybe change your loop to this?

Please Login or Register  to view this content.

[nahge123]

You need to use Code Tags in your post.

You cannot copy an entire row & start in C, the sizes will not match

Can you give me more info on that? New to VBA. Do I need to use a range instead of cl.entirerow? Code? Thank you for your help.

[nahge123]

Pasted the code in place of mine and now it only pastes the last row of the data, but it is going into column C. Further ideas?

[nahge123]

the current code will paste the rows of data in column A on the "check List". I need it the rows to start in column C instead of A on "Check List". I understand that I can't paste an entire row on "Check List" starting in column C, but don't know how to shorten the range from entirerow.

Thank you

[JOHN H. DAVIS]

Did you try the suggestion in Post 4?

[nahge123]

Pasted the code in place of mine and now it only pastes the last row of the data, but it is going into column C.

[JOHN H. DAVIS]

Based on reading this thread. I thought you wanted it to go into Column C? Is that correct?

[nahge123]

Yes I want it to paste the row starting in column c on "check list". But there are multiple columns, and the number of rows will vary. And the new code is only copying the last row.

[JOHN H. DAVIS]

The only difference in the new code and the old code is that 1) It changes the range to copy from EntireRow to the Columns, specified and 2) It changes the destination from Column A to Column C using the offset function. If the original code worked, then I can't see why those revisions would have changed the result? Can you attach a sample of your workbook?