Consulting

Results 1 to 5 of 5

Thread: Copy and paste VBA

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location

    Copy and paste VBA

    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

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,883
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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

  3. #3
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location
    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

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,883
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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

  5. #5
    VBAX Regular
    Joined
    Dec 2019
    Posts
    11
    Location
    Thank you with all my heart.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •