Macro commands

[bigsistar12]

I am pretty new to Visual Basic and Macros. I can create general easy macros but this one Im scratching my head at.

I am trying to create a spreadsheet that takes information from a form I create and it copies the values within the form cells over to a spreadsheet that only needs the current values.

This is what I am doing

Sheets("Pricing Calculator").Select
Range("A2:D2").Select
ActiveCell.FormulaR1C1 = "michelle"
Sheets("worksheet 2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "michelle"

the problem is that when i run this macro from pricing calculator to worksheet 2 it always populates the name michelle. I obviously know this is wrong but how do i get the current value only to be copied from one cell in pricing calculator to be pasted in worksheet two active cell? If I copy the cell entirely it pastes over with cell properties (ex color and size), which I dont want. My calculator is going to be updated and changed constantly but the new information that is added there needs to be saved in a master spreadsheet (wksht 2) not repopulating the old information.

example:
Sheets("worksheet 2").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Sheets("Pricing Calculator").Select
Range("A2:D2").Select
ActiveCell.FormulaR1C1 = "michelle"
Sheets("worksheet 2 ").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "michelle"
Sheets("Pricing Calculator").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "464604646"
Sheets("worksheet 2 ").Select
Range("B2").Select

id appreciate any help i can get.

 

[craig.penny]

Welcome to the boards. If no one answers you in a day or so, don't get discouraged just repost your question and try to think of ways to make it easier to understand what it is that you're asking.

This is what I just came up with:

Sub CopyFormula()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = ActiveWorkbook.Sheets("Sheet1")
Set s2 = ActiveWorkbook.Sheets("Sheet2")
s2.Cells(3, 3).Formula = s1.Cells(2, 2).Formula
End Sub

I know this isn't exactly what you are asking but I don't quite understand what you want. If you want to copy just formulas this is probably a good starting point for you since it won't copy formats or anything else, just the formula.

Now if it's just the value you want to copy replace

s2.Cells(3, 3).Formula = s1.Cells(2, 2).Formula

with

s2.Cells(3, 3).Value = s1.Cells(2, 2).Value

Just for clarity - I have two sheets "Sheet1" and "Sheet2". In cell "B2" on "Sheet1" I have a formula. After I run this macro, the same formula appears on "Sheet2" in cell "C3".

Hope that helps.

Try to mess around with this and then ask questions to clear up any areas that aren't making sense.