Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    *bump*

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One way to do this;

    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
    With Ws2
    Ws1.[d3].Resize(7, 1) = .Range(.Cells(3, c), .Cells(10, c)).Value
    Ws1.[h3].Resize(7, 1) = .Range(.Cells(3, c + 1), .Cells(10, c + 1)).Value
    End With

    End Sub


    Ivan

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

    Default

    thx

Some videos you may like

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
  •