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

JECYN

New Member
Joined
Dec 6, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    27.7 KB · Views: 15

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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:
VBA Code:
Sheets("Sheet2").Range("A3:A" & lastrow)
is constaint so when you paste it, it always overwrite at last cell of Column A
 
Upvote 0
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    38.1 KB · Views: 21
Upvote 0
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:
VBA Code:
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:
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top