VBA setting times of Copy & paste specific columns if the value is greater than 1

[JECYN]

I am currently working a script that if the Col E is greater than 1, then VBA is to perform copy col A & Col C data into another spreadsheet.
Follow the Col E data. If col E is 1, means copy 1 time, if Col E is 10, then need to perform copy & paste 10 times.

With below VBA, I not sure why is there an error point while it prompt error type mismatch for <For n = 1 To k>..
Is there any suggestion?

Dim myrange As Range
Dim k As Range
Dim lastrow As Long
Dim n As Integer

Sheets("Sheet1").Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row


Set myrange = Range("E3:E" & lastrow)

For Each k In myrange
If k.Value > 1 Then
For n = 1 To k
k.Offset(0, -4).Copy
Sheets("Sheet2").Range("A3:A" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next n

End If
Next

 

[eiloken]

I am currently working a script that if the Col E is greater than 1, then VBA is to perform copy col A & Col C data into another spreadsheet.
Follow the Col E data. If col E is 1, means copy 1 time, if Col E is 10, then need to perform copy & paste 10 times.

With below VBA, I not sure why is there an error point while it prompt error type mismatch for <For n = 1 To k>..
Is there any suggestion?

Dim myrange As Range
Dim k As Range
Dim lastrow As Long
Dim n As Integer

Sheets("Sheet1").Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row


Set myrange = Range("E3:E" & lastrow)

For Each k In myrange
If k.Value > 1 Then
For n = 1 To k
k.Offset(0, -4).Copy
Sheets("Sheet2").Range("A3:A" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next n

End If
Next

ofcourse it will error because k is not numberic type, you should change it to: For n = 1 To k.value

 

[eiloken]

I am currently working a script that if the Col E is greater than 1, then VBA is to perform copy col A & Col C data into another spreadsheet.
Follow the Col E data. If col E is 1, means copy 1 time, if Col E is 10, then need to perform copy & paste 10 times.

With below VBA, I not sure why is there an error point while it prompt error type mismatch for <For n = 1 To k>..
Is there any suggestion?

Dim myrange As Range
Dim k As Range
Dim lastrow As Long
Dim n As Integer

Sheets("Sheet1").Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row


Set myrange = Range("E3:E" & lastrow)

For Each k In myrange
If k.Value > 1 Then
For n = 1 To k
k.Offset(0, -4).Copy
Sheets("Sheet2").Range("A3:A" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next n

End If
Next

beside:

Sheets("Sheet2").Range("A3:A" & lastrow)

is constaint so when you paste it, it always overwrite at last cell of Column A

 

[JECYN]

Thank you for the reminders!
I've add the <.value> after for n= 1 to k.

However, the Macro result is not what I want..
it only paste 1 time, it should be follow the referencing cell value to perform copy paste times.

Dim myrange As Range
Dim k As Range
Dim lastrow As Long
Dim n As Integer
Dim lastrowpaste As Long


Sheets("Sheet1").Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row

lastrowpaste = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

Set myrange = Range("I3:I" & lastrow)

For Each k In myrange
If k.Value > 1 Then
For n = 1 To k.Value
k.Offset(0, -8).Copy
Sheets("Sheet2").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next n

End If

 

[eiloken]

Thank you for the reminders!
I've add the <.value> after for n= 1 to k.

However, the Macro result is not what I want..
it only paste 1 time, it should be follow the referencing cell value to perform copy paste times.

Dim myrange As Range
Dim k As Range
Dim lastrow As Long
Dim n As Integer
Dim lastrowpaste As Long


Sheets("Sheet1").Activate

lastrow = Range("A" & Rows.Count).End(xlUp).Row

lastrowpaste = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

Set myrange = Range("I3:I" & lastrow)

For Each k In myrange
If k.Value > 1 Then
For n = 1 To k.Value
k.Offset(0, -8).Copy
Sheets("Sheet2").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Next n

End If

like i said, your lastrowpaste is constant so you no matter how many time you copy and paste the value, it will keep overwrite at the cell you paste, to paste it for k times, you should change the code like this:

Set myrange = Range("I3:I" & lastrow)

For Each k In myrange
    If k.Value >= 1 Then
        For n = 1 To k.Value
            lastrowpaste = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
            k.Offset(0, -8).Copy
            Sheets("Sheet2").Range("A" & lastrowpaste + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Application.CutCopyMode = False
        Next n
    End If
Next k

or like this in my recommend:

Set myrange = Range("I3:I" & lastrow)

For Each k In myrange
    If k.Value >= 1 Then
        For n = 1 To k.Value
            lastrowpaste = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
            Sheets("Sheet2").Range("A" & lastrowpaste + 1).Value = k.Offset(0, -8).Value
        Next n
    End If
Next k

 

[JECYN]

Great, it solved my doubts!
Thank you for the sharing.