Copy and paste row in new sheet with changes in cell value (month) based on other cell value
AR my
I have one table of activity, with X as frequency (once every X month) and first start date and end date as below:
How do I copy each row and paste it in a new sheet, with additional row for each based on X and increment in month date as below:
Vasily Ivoyzha
here solution based on described issue
Sub test()
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Dim x&, cnt&, cl As Range, SDt$, EDt$, Dif As Date, Key As Variant
With Sheets("Source")
x = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cl In .Range(.[A2], .Cells(x, "A"))
cnt = 1
Dic.Add cnt & ";" & cl.Text & ";" & cl.Offset(, 2).Text & ";" & cl.Offset(, 3).Text, Nothing
Dif = DateAdd("m", cl.Offset(, 1).Value, cl.Offset(, 3).Value)
While Year(Dif) = 2015
cnt = cnt + 1
SDt = Right("0" & Month(Dif), 2) & "-" & Right("0" & Day(cl.Offset(, 2).Value), 2) & "-" & Year(Dif)
EDt = Right("0" & Month(Dif), 2) & "-" & Right("0" & Day(cl.Offset(, 3).Value), 2) & "-" & Year(Dif)
Dic.Add cnt & ";" & cl.Text & ";" & SDt & ";" & EDt, Nothing
Dif = DateAdd("m", cl.Offset(, 1).Value, Dif)
Wend
Next cl
End With
Sheets("Output").Activate: x = 2 ''
With Sheets("Output")
For Each Key In Dic
.Range(.Cells(x, 1), Cells(x, 4)) = Split(Key, ";")
x = x + 1
Next Key
.[C1:D1].Value = Sheets("Source").[C1:D1].Value
.[B1] = Sheets("Source").[A1]
.[A1] = "TASK ITERATION"
End With
End Sub
Initial sheet
Output sheet
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Add JavaScript onclick() to HTML form submit
- Next: Java Bioinformatics - get all indexes of multiple specific words in a string
Related
Related Related
- 1
add another row and paste value based on cell value of another sheet
- 2
Copy Row To Another Sheet Based On Cell Value In Google Sheet
- 3
Copy and paste the row a N number of times (N based on a value in the cell)
- 4
excel copy row to another sheet based on cell value
- 5
Copy Specific data (Not the entire row!) to another sheet based on cell value
- 6
Macro to Copy Range and Paste Based on Cell Value
- 7
VBA Cut entire row based on text in cell and paste to new sheet
- 8
Find value from one cell, copy value from cell near to that cell and paste it to another sheet
- 9
Need to copy a row from one spreadsheet "A" to ""B" based on a cell value in "A" and paste it in a specific row in "B" based
- 10
Excel Macro to copy select cells from a row from one sheet to another based upon a cell value
- 11
how to copy multiple row to another sheet in specific cell based on value excel VBA
- 12
excel copy cell value to new sheet if column a = "x"
- 13
Excel copy cell value to another cell if cell value changes
- 14
Excel copy cell value to another cell if cell value changes
- 15
how to get cell value in other sheet based on condition?
- 16
Google Sheets: Move a row of data to another sheet based on cell value
- 17
Google Sheets: Move a row of data to another sheet based on cell value
- 18
How can I reference a row in another sheet based on value in cell?
- 19
Add a value in a cell based on changes to another cell
- 20
Locate a phrase, copy the row and then locate non-empy cell above and paste the lot onto a new sheet
- 21
Copy cell from on sheet to another, if other cell in row matches values
- 22
If cell value changes, copy value to another worksheet
- 23
Locking cell based on value on other cell
- 24
Locking cell based on value on other cell
- 25
Clear contents of cell based on value in other cell
- 26
How to change cell value repeatedly by entering the value in other cell or sheet?
- 27
Excel VBA for each cell in range A, copy value in cell B, to new sheet
- 28
Remove row based on cell value
- 29
Excel 2010 VBA: Copy a row to a new sheet based on value
Comments