copy a range in an IF statement

[Gandalf]

Hi all,

A quick question please as it gas been a while since I used a spreadsheet
and I seem to be stuck on something that should be simple.

I am doing an automated time sheet for my wife, well her work anyway, and I
can not get to copy a range in an IF statement.

In English it is like this :-

IF A=1 THEN copy "range1" to "range position 1" ELSE copy "range2" to "range
position 1"


I can do single cells but not a range which I think should be possible. If I
do it with a formula in each cell it becomes too unwieldy but possible, any
changes means I have to change each formula which will be in the region of
150 to 300 formulas to be redone.

Each of the ranges are formatted and the same size as the area they are
being copied to.

Any help gratefully appreciated.

 

[Don Guillett]

I would define each range and give it a name or just use the range>then use
a macro to copy/paste

range(rng1).copy range("b1")
or
range("a1:a21").copy range("b1")

 

[Gandalf]

Many thanks Don

Macro's have advanced since I last used them but I have some knowledge of
Visual Basic which helped me get this as a basic principle

-----------------------------------------------------------------------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16/05/2009 by Chas
'
' Keyboard Shortcut: Ctrl+Shift+M
'
If Range("A1") = 1 Then
Range("C6:L6").Select
Selection.Copy
Range("C11").Select
ActiveSheet.Paste
Else
Range("C5:L5").Select
Selection.Copy
Range("C11").Select
ActiveSheet.Paste
End If


End Sub
---------------------------------------------------------------------------------------------

Now I have two options I think.

1. Can I automate this macro to run like a formula would

or

2. Is it best to automate the whole process which is date entry driven which
selects various work times depending on the week no.

My common sense says the second one but I thought I might ask one with more
recent common sense so to speak.

Either way, sincere thanks to you Don.

 

[Dave Peterson]

Or without the .select's:

Option Explicit
Sub DoTheCopy()
'
' Macro1 Macro
' Macro recorded 16/05/2009 by Chas
'
' Keyboard Shortcut: Ctrl+Shift+M
'
With ActiveSheet
If .Range("A1").value = 1 Then
.Range("C6:L6").copy _
destination:=.Range("C11")
Else
.range("C5:L5").copy _
destination:=.Range("C11")
End If

End Sub

 

[Don Guillett]

This also works without selections. row 5 for 0 or row 6 or anything else
Sub copybasedona1()
Cells(5 + Application.Min(1, Range("a1")), "c").Resize(, 10).Copy
Range("c11")
End Sub

 

[Gandalf]

Many thanks Don and Dave, I will work along the VB route and refresh my
programming abilities to get this done. Thinking about the whole problem
with this in mind has led to new concept in solving the whole sheet.