Macro help
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro help

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to make a macro so that it can copy specific cell according to a value in another cell, and paste it to another cell.
    I.e. in “order” sheet , cell G1, if the user type “1” in, then the macro go to sheet 2 and copy cells “B3:B18” and paste the value to sheet 1 cells “D3:D18” ,also copy “sheet 2” cell “C3:C18” and paste the value to sheet 1 cell “H3:H18”.
    If the user type 2 in cell G1, then when he ran the macro, it will copy cells “D3:D18” and paste the value to “order” sheet cells “D3:D18”, also copy “sheet 2” cell “E3:E18” and paste the value to sheet 1 cell “H3:H10”… and if type 3, then it will copy F3:F18 and G3:G18 and so on…

    In “order” sheet, I got the macro:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$G$1" And IsNumeric([G1]) Then
    If [G1] - Int([G1]) = 0 Then Copy_Paste
    End If
    End Sub

    Then I assign this macro to a button

    Sub Copy_Paste()
    Dim ws1 As Worksheet, ws2 As Worksheet, c As Integer
    Set ws1 = Worksheets("Order")
    Set ws2 = Worksheets("Sheet2")
    c = ws1.[G1].Value * 2
    ws2.Range(ws2.Cells(3, c), ws2.Cells(10, c)).Copy ws1.[D3]
    ws2.Range(ws2.Cells(3, c + 1), ws2.Cells(10, c + 1)).Copy ws1.[H3]
    End Sub

    The prob comes as I tried to make it so it only copy the value, I tried the following macro but didn’t work ;/

    Sub Copy_Paste()
    Dim ws1 As Worksheet, ws2 As Worksheet, c As Integer
    Set ws1 = Worksheets("Order")
    Set ws2 = Worksheets("Sheet2")
    c = ws1.[G1].Value * 2
    ws2.Range(ws2.Cells(3, c), ws2.Cells(10, c)).Copy ws1.[D3]
    PasteSpecial Paste:=xlValues
    ws2.Range(ws2.Cells(3, c + 1), ws2.Cells(10, c + 1)).Copy ws1.[H3]
    PasteSpecial Paste:=xlValues
    End Sub

    Plz help

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This POST should be in the Macro forum.

    It will be moved there in three (3) hours.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    JPG Hi mate --

    Ive read lots of posts and many posters are posting in wrong section guess we have to get used to that one -

    If you are moving then i guess thats fine by me and pass on well done

    Rdgs
    Jack :0

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com