Home > database >  Changing a value of 2 cells and corresponding formula values need to be copied into different sheet
Changing a value of 2 cells and corresponding formula values need to be copied into different sheet

Time:10-27

Need to change the values and copy the corresponding values to the new sheet. I wrote

Sub Macro2()
Sheets.Add After:=ActiveSheet
    'Worksheets("Sheet2").Range("A1").Value=Worksheets("Sheet1").Range("A1").Value
    For x = 0.25 To 5
    Worksheets("Sheet2").Range("B3:B6").Value = Worksheets("Sheet2").Range("A6:A9").Value
       Z = 3
        For y = 10 To 100
           
       Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = x
Range("B3").Select
ActiveCell.FormulaR1C1 = y
Range("B6:B9").Select
Selection.Copy
Sheets("Sheet2").Select
Cells(Z, 3).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
        Z = Z   1
        y = y   10
        Next y
        
    x = x   0.25
    Next x
 End Sub

enter image description here enter image description here the variable1 need to be changed from 0.25 to 5 with increment of 0.25. The second variable2 changed from 10 to 100 . The corresponding values need to be pasted in the new sheet similar to the one showed in image 2 which is not completely shown in the image. My code is basically performing like in this image.enter image description here. How do I change to reflect like the completed image of 2.

CodePudding user response:

This works

 Sub Macro2()

     Sheets.Add(After:=Sheets("Sheet1")).Name = "NewS"
     w = 3

    For x = 0.25 To 5 Step 0.25
    'Getting value1..4
    Worksheets("NewS").Range(Cells(w, 2), Cells(w, 2).Offset(3, 0)).Value = Worksheets("Sheet1").Range("A6:A9").Value
    'Getting the value x
    Worksheets("NewS").Cells(w, 2).Offset(-1, 0).Value = x
    
       Z = 3
        For y = 10 To 100 Step 10
           
       Sheets("Sheet1").Select
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = x
Range("B3").Select
ActiveCell.FormulaR1C1 = y
Range("B6:B9").Select
Selection.Copy
Sheets("NewS").Select
Cells(w, Z).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

  'Getting the y value
    Cells(w, Z).Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = y
        Z = Z   1
        
        Next y
        w = w   6
       
    Next x
End Sub

The output looks like below enter image description here

  • Related