Copy and paste VBA

[baxius]

Hello, i'd like to ask for anyones help or advice on how to create this. Then thing i need is that When a value is entered inside a certain cell, a new table would be copied from previous cells into the adjecent cells. For example (Worksheet in attachments). IF any value is entered in G23, then Value and format from Green table should be copied below (Red table) and so on with every 12th G row value. Thank you for your help or any kind of tips.2552325523

[Paul_Hossler]

Welcome to the forum -- please take a minute and review the links in my signature, esp the FAQs


This goes into the code page of Sheet1 -- the attachment shows where

I THINK this is what you asked for

If not, ask again




Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

Set r = Target.Cells(1, 1)

If Len(r.Value) = 0 Then Exit Sub

If r.Column <> 7 Then Exit Sub
If r.Row Mod 11 <> 1 Then Exit Sub


r.Offset(-11, -6).Resize(11, 5).Copy r.Offset(0, -6)


End Sub

[baxius]

Works like a charm. Could you explain how to adjust the size of Copied table. And if possible could you make that it worked on all G row cells, not just every 12th

[Paul_Hossler]

1.



If r.Row Mod 11 <> 1 Then Exit Sub


r is the first cell of the changed range on the WS and it has a row number

.Row = 12 so 12 Mod 11 = 1
.Row = 24 so 24 Mod 11 = 1
.Row = 36 so 36 Mod 11 = 1, etc.

Right now it says that if the changed row number Mod 11 is NOT 1 then just exit

so for all rows, just comment out that line

2.



r.Offset(-11, -6).Resize(11, 5).Copy r.Offset(0, -6)


r is the first cell of the changed range on the WS

.Offset(-11, -6) says to get the cell that is 11 rows UP and 6 columns to the LEFT

.Resize(11,5) says to get the range from the above cell that has 11 rows and 5 columns

r.Offset(0, -6) says to put the above block of cells in the same row as r, but 6 columns to the LEFT


The online help is pretty good so put your cursor on the VBA that you want to know more about and hit F1

[baxius]

Thank you with all my heart.