Vba copy from sheet1 and paste to sheet2

Krissariet

New Member
Joined
Aug 28, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi! I need help with a vba. I need to copy a selected row only if i selected it from sheet 1 and paste it to sheet 2 in the same location for a number of times that is in sheet 1 in J cloumn.
For example I need to copy data from sheet1 to sheet2 from A2 to I2 the number of times that is in J2 and then select data to A3 to I3 the number of times that is in I3 but just when I selected it. Hope you can help me and please forgive me for my english. Thank you!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Thank you very much!!! It is very helpful but not quite what I need. I need to copy to the sheet2 only when I select the row the number of times that are in J in that row, not the entire rows. For exemple if I need the data of the first row i have to select that data and it hase to be copied the number of times that are in the J1
And then if I need the data from the 5th row I have to select that data and it hase to be copied the number of times that are in J5.
Thank you very much!!! It is very helpful but not quite what I need. I need to copy to the sheet2 only when I select the row the number of times that are in J in that row, not the entire rows. For exemple if I need the data of the first row i have to select that data and it hase to be copied the number of times that are in the J1
And then if I need the data from the 5th row I have to select that data and it hase to be copied the number of times that are in J5.
See if this works for you.

VBA Code:
Sub CopyLines()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcLRow As Long, destLRow As Long
    Dim srcArr As Variant, destArr() As Variant
    Dim NoOfLines As Long, destRow As Long
    Dim iRow As Long, iCol As Long, iLines As Long
  
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
  
    srcLRow = srcSht.Range("A" & Rows.Count).End(xlUp).Row
    Set srcRng = srcSht.Range("A2:J" & srcLRow)
    srcArr = srcRng
    NoOfLines = Application.Sum(srcRng.Columns(10))
    ReDim destArr(1 To NoOfLines, 1 To UBound(srcArr, 2))
  
    For iRow = 1 To UBound(srcArr)
        For iLines = 1 To srcArr(iRow, 10)
            destRow = destRow + 1
            For iCol = 1 To UBound(srcArr, 2)
                destArr(destRow, iCol) = srcArr(iRow, iCol)
            Next iCol
        Next iLines
    Next iRow
  
    destSht.Range("A2").Resize(UBound(destArr, 1), UBound(destArr, 2)).Value = destArr

End Sub

Thank you very much!!! It is very helpful but not quite what I need. I need to copy to the sheet2 only when I select the row the number of times that are in J in that row, not the entire rows. For exemple if I need the data of the first row i have to select that data and it hase to be copied the number of times that are in the J1
And then if I need the data from the 5th row I have to select that data and it hase to be copied the number of times that are in J5.

Thank you very much!!! It is very helpful but not quite what I need. I need to copy to the sheet2 only when I select the row the number of times that are in J in that row, not the entire rows. For exemple if I need the data of the first row i have to select that data and it hase to be copied the number of times that are in the J1
And then if I need the data from the 5th row I have to select that data and it hase to be copied the number of times that are in J5.
Thank you very much!!! It is very helpful but not quite what I need. I need to copy to the sheet2 only when I select the row the number of times that are in J in that row, not the entire rows. For exemple if I need the data of the first row i have to select that data and it hase to be copied the number of times that are in the J1
And then if I need the data from the 5th row I have to select that data and it hase to be copied the number of times that are in J5.
See if this works for you.

VBA Code:
Sub CopyLines()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcLRow As Long, destLRow As Long
    Dim srcArr As Variant, destArr() As Variant
    Dim NoOfLines As Long, destRow As Long
    Dim iRow As Long, iCol As Long, iLines As Long
  
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
  
    srcLRow = srcSht.Range("A" & Rows.Count).End(xlUp).Row
    Set srcRng = srcSht.Range("A2:J" & srcLRow)
    srcArr = srcRng
    NoOfLines = Application.Sum(srcRng.Columns(10))
    ReDim destArr(1 To NoOfLines, 1 To UBound(srcArr, 2))
  
    For iRow = 1 To UBound(srcArr)
        For iLines = 1 To srcArr(iRow, 10)
            destRow = destRow + 1
            For iCol = 1 To UBound(srcArr, 2)
                destArr(destRow, iCol) = srcArr(iRow, iCol)
            Next iCol
        Next iLines
    Next iRow
  
    destSht.Range("A2").Resize(UBound(destArr, 1), UBound(destArr, 2)).Value = destArr

End Sub
Hi everyone, this forum is an authentic gold mine for a newbie like me.
This pice of code is just i was looking for,but i need it to copy with format and values for all cells.
Can you please help me with it?
My coding skills are limited.
Please and thank you so very much!
See if this works for you.

VBA Code:
Sub CopyLines()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcLRow As Long, destLRow As Long
    Dim srcArr As Variant, destArr() As Variant
    Dim NoOfLines As Long, destRow As Long
    Dim iRow As Long, iCol As Long, iLines As Long
   
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
   
    srcLRow = srcSht.Range("A" & Rows.Count).End(xlUp).Row
    Set srcRng = srcSht.Range("A2:J" & srcLRow)
    srcArr = srcRng
    NoOfLines = Application.Sum(srcRng.Columns(10))
    ReDim destArr(1 To NoOfLines, 1 To UBound(srcArr, 2))
   
    For iRow = 1 To UBound(srcArr)
        For iLines = 1 To srcArr(iRow, 10)
            destRow = destRow + 1
            For iCol = 1 To UBound(srcArr, 2)
                destArr(destRow, iCol) = srcArr(iRow, iCol)
            Next iCol
        Next iLines
    Next iRow
   
    destSht.Range("A2").Resize(UBound(destArr, 1), UBound(destArr, 2)).Value = destArr

End Sub
 
Upvote 0
Hi everyone, this forum is an authentic gold mine for a newbie like me.
This pice of code is just i was looking for,but i need it to copy with format and values for all cells.
Can you please help me with it?
My coding skills are limited.
Please and thank you so very much!

You are better off starting a new thread. If you leave me a link to the new thread here I will take a look.
Make sure you show us the code you are using and clarify what is or isn't working for you.
Provide the following info in your question.
When you say all the cells, where is the data starting from (eg A1 or somewhere else) ?
What column can be relied on to determine what should be the last row ?
Where do you want to copy to start (eg A1 or somewhere else ?
 
Upvote 0
See if this works for you.

VBA Code:
Sub CopyLines()

    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcLRow As Long, destLRow As Long
    Dim srcArr As Variant, destArr() As Variant
    Dim NoOfLines As Long, destRow As Long
    Dim iRow As Long, iCol As Long, iLines As Long
  
    Set srcSht = Worksheets("Sheet1")
    Set destSht = Worksheets("Sheet2")
  
    srcLRow = srcSht.Range("A" & Rows.Count).End(xlUp).Row
    Set srcRng = srcSht.Range("A2:J" & srcLRow)
    srcArr = srcRng
    NoOfLines = Application.Sum(srcRng.Columns(10))
    ReDim destArr(1 To NoOfLines, 1 To UBound(srcArr, 2))
  
    For iRow = 1 To UBound(srcArr)
        For iLines = 1 To srcArr(iRow, 10)
            destRow = destRow + 1
            For iCol = 1 To UBound(srcArr, 2)
                destArr(destRow, iCol) = srcArr(iRow, iCol)
            Next iCol
        Next iLines
    Next iRow
  
    destSht.Range("A2").Resize(UBound(destArr, 1), UBound(destArr, 2)).Value = destArr

End Sub

You are better off starting a new thread. If you leave me a link to the new thread here I will take a look.
Make sure you show us the code you are using and clarify what is or isn't working for you.
Provide the following info in your question.
When you say all the cells, where is the data starting from (eg A1 or somewhere else) ?
What column can be relied on to determine what should be the last row ?
Where do you want to copy to start (eg A1 or somewhere else ?
Hi Alex, thank you very much for the reply, here is the link from the thread I just posted
vba excel copy-paste all data x number of times between sheet 1 and 2
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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