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.Copy-paste vba.xlsmCopy-paste vba.xlsm
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
1.
r is the first cell of the changed range on the WS and it has a row numberIf r.Row Mod 11 <> 1 Then Exit Sub
.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 is the first cell of the changed range on the WSr.Offset(-11, -6).Resize(11, 5).Copy r.Offset(0, -6)
.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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you with all my heart.
Posting Permissions
Reply With Quote
