If then statements

griffen5757

New Member
Joined
Nov 11, 2002
Messages
2
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

New Member
Joined
Nov 11, 2002
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top