--------------------------------------------------------------------------------
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
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