If then statements

[griffen5757]

I'm pretty new to this and I'm having a problem with this, any help is appreciated.
I have four workbooks PM1,PM4,PM5 and SCANDATA. If A1 in SCANDATA = 1 then I need to copy and paste D5,H5 and I5 values to PM1 B6,C6 and D6. Maybe I'm way off but this is what I've tried.

Sheets("SCANDATA").Select
If A5 = 1 Then
Range("D5,H5:I5").Select
Range("H5").Activate
Selection.Copy
Sheets("PM1").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
End Sub

 

[Tom Urtis]

Your macro would look something like this:

Sub ScanDataToPM1()
If Worksheets("SCANDATA").Range("A5").Value<> 1 Then Exit Sub
Worksheets("PM1").Range("B6").Value = Worksheets("SCANDATA").Range("D5").Value
Worksheets("PM1").Range("C6").Value = Worksheets("SCANDATA").Range("H5").Value
Worksheets("PM1").Range("D6").Value = Worksheets("SCANDATA").Range("I5").Value
End Sub

Three points:

(1)
This could be done automatically with a sheet change event, so that when a 1 is entered into A1 of SCANDATA, the values are copied. However, you did not say if that 1 value is manually entered or returned as a result of a formula in A1, and that fact would need to be known for the sheet code to be written properly.

(2)
You could place formulas in B6, C6, and D6 of PM1 to return the values in D5, H5, and I5 of SCANDATA if A1 of SCANDATA equals 1. In other words, no macro is needed here, unless you have a larger reason for wanting one.

(3)
Since you're new with VBA (as you say), here's a tip: Notice that your macro does a bunch of selecting, activating, and copying, which is not necessary; many VBA operations can take place without selecting any cells or ranges, such as the code I suggested.

_________________
Tom Urtis
This message was edited by Tom Urtis on 2002-11-13 01:38

 

[griffen5757]

Thanks Tom

I'll look at your suggestions. I collect data every day with a data extractor. I needed a way to put the 1s data in PM1 sheet, 4s data in PM4 sheet and 5s data in PM5 sheet "up to 500 rows of data".
I tried to do the formatting in the cells "using IF" but, I couldn't get the copy and past to work in the cell.