Copy and append data to a separate worksheet using VBA

[BlurSotong]

Hi all

Been looking around and couldn't find a suitable VBA code. (I'm not well-versed in coding thus don't know enough to update the codes that I found online). Hoping I can get some help here.

I have 2 worksheets.

Worksheet1: Data is entered from
Column A
Column B
Column C
Column D
Column E
Column F
Column G
Column H - No. of times to multiple the data in Worksheet2

I need to copy and append the data from Worksheet1.Column A to G to Worksheet2.Column C to I based on the number specified in Worksheet1.Column H. The number differs for every row. E.g. If for Row 10, the value in Column H is 6 means I have to duplicate the data in Row 10, 6 times in Worksheet2. If for another row, the number is 15, I have to duplicate the corresponding data 15 times in Worksheet2.

Data in Worksheet1 is an operation worksheet thus the data will be entered on different days. Will only want to copy and append the new data or to select the specific row to be copied. Also when doing the copy, the new data has to append to the existing data in Worksheet 2 (i.e. no overwriting).

Hope the experts in this Forum will be able to help me .

Thanks so much in advance.

 

[mumps]

Re: Need help with copy and append data to a separate worksheet using VBA

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Save your workbook as a macro-enabled file. This is a Worksheet_Change macro that is triggered automatically when you enter a value in column H in your case and exit the cell. So if you enter the number 6 in cell H10 and exit the cell, the range from column A to column G in that row will be copied six times to Sheet2 starting in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Resize(Target.Value)
    Application.CutCopyMode = False
End Sub

 

[BlurSotong]

Re: Need help with copy and append data to a separate worksheet using VBA

Thanks so much! It works very well

 

[mumps]

Re: Need help with copy and append data to a separate worksheet using VBA

You are very welcome.