I will reply this evening Graig

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Craig Handley" wrote in message ...
Hi Ron,

Sorry i just re-read your post ... yes i would like to paste the values only.


Thanks,

Craig



"Craig Handley" wrote:

Hi Ron,

Thank you for the reply.

I tried that bit of code you supplied but it didn't copy anything into the
TESTTARGET workbook .... not sure if it's because there is no 'paste' etc at
the end of the line.

With the loop suggestion will it still work where i'm not copying every
sheet in the workbook ? They're not even blocked sequential i.e. sheets 3-15.
It would be more like sheets 4-9, 10-18, 20-25, etc ..... not sure it would
tie up easily in the target workbook so might be easier to have line for each
sheet where i can put the sheet names in (they will be the same in both
workbooks e.g. ABFI, ABOP etc).

Any ideas how i could set it up to replace the Range & destination easily
each month ? I messed about with (Range(Cells(3,n),Cells(3,n)) a bit
yesterday and was getting there i think but not sure if that's the correct
path to go down?

My thought then was to prompt the user to enter the period (e.g April to us
= 1, May = 2 etc) .... i could then use that number to get the value for 'n'.

Hope that makes sense and thanks again for your help.

Regards,

Craig.

"Ron de Bruin" wrote:

Hi Craig

In this example the code copy from the activeworkbook

You can repeat this line for the sheets you want
If you want to do the same for all sheets we can make a loop

Wb1.Sheets("Sheet1").Range("A3:A22").copy Wb2.Sheets("Sheet1").Range("A15")

If you want to copy as values post back

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks("TESTTARGET.xls")

Wb1.Sheets("Sheet1").Range("A3:A22").copy Wb2.Sheets("Sheet1").Range("A15")

Application.ScreenUpdating = True
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Craig Handley" <Craig wrote in message
...
Hi there,

Sorry if this appears twice - first attempt crashed on posting so trying to
re-submit my question again.

Ok here goes. I'm trying to automate a monthly process where i copy a range
of cells from various sheets in a workbook and paste the values into another
workbook.

So for example I copy A3:A22 from sheet1 in a workbook (lets call it
TESTSOURCE.xls) and paste the values to A15 in sheet1 in another workbook
(lets call it TESTTARGET.xls). I then copy sheet 2 to sheet 2, sheet 3 to
sheet 3 etc etc. I'm sure you get the idea.

I've managed to come up with some code that does this for me (not saying
it's efficient etc but it seems to work).

Sub Test1()

Workbooks("TESTSOURCE.xls").Activate
Sheets("Sheet1").Range("A3:A22").Copy

Workbooks("TESTTARGET.xls").Activate
Sheets("Sheet1").Range("A15").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False



Workbooks("TESTSOURCE.xls").Activate
Sheets("Sheet2").Range("A3:A22").Copy

Workbooks("TESTTARGET.xls").Activate
Sheets("Sheet2").Range("A15").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub



However the next month i need to copy Range B3:B22 to B15, the month after
that it's C3:C22 to C15 ... so on and so forth... again i'm sure you see the
pattern/idea.

Now i could just go into the code and use find/replace each month to change
the copy range and destination but i'm trying to find a way to made set the
variable at the top of the code so that i (or other users) only need to
change it once and the code still works correctly. Anything i've tried thus
far keeps giving me range/class errors and the likes so i'm hoping someone
might be able to point me in the right direction?

Even better would be if i could call an input box which asked the user to
enter the source range and then specify the destination cell. Would that be
possible/easy to add in ?

Hope that makes sense but please let me know if anything i'm trying to do is
unclear.

Thanks in advance for your help.

Regards,
Craig