copying and paste macro

[Mr Marvin]

i am using this macro to copy cells which have been selected via checkboxes. The cells start in A1 but when they are pasted into output they are found in cell A10000 odd where i need them to be in A1. Is there something i am missing here.

Also when it is copying the cells over i am copying the entire row where i just need the info in the A column when selected. can anyone assist please

thanks

Sub MoveRowBasedOnCellValue()
'Updated by Extendoffice 2017/11/10
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Email Template").UsedRange.Rows.Count
J = Worksheets("Output").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Output").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Email Template").Range("C1:C" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "True" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Output").Range("A" & J + 1)
J = J + 1
End If
Next
End Sub

 

[SidharthS]

Most probably its bcos at row 10000 there's some value in present making the whole range Used Range

try clearing everything in "Output Sheet" or just delete it and create a new sheet of the same name.

 

[Mr Marvin]

@SidharthS thank you.

do you know how the code can be adjusted so that it just copies the text in column A and not the entire row. TIA

 

[MARK858]

do you know how the code can be adjusted so that it just copies the text in column A and not the entire row. TIA

Try changing

xRg(K).EntireRow.Copy Destination:=Worksheets("Output").Range("A" & J + 1)

to

xRg(K).Offset(, -2).Copy Destination:=Worksheets("Output").Range("A" & J + 1)

 

[Mr Marvin]

thank you for all your assistance