Can anyone advise on (simple?) Excel macro?

  montyburns 10:50 13 Jun 2008

OK, I know I should learn to VBA myself, although I really have had a go at this, using VBA from other workbooks I have and LOTS of Googling!

Getting a bit stumped now though.

I'm trying to copy a range of data from one worksheet/workbook ("Plan" on "Staff Planner")to another ("Plan" on "Planner") The first workbook is accessed by me to set up staff rotas, the second is on a shared drive which my staff have read-only access to

Decided I didn't want the whole of every sheet copied from one workbook to another (after I'd found a way to do this!) and that I'd rather do a range instead. Seems easy enough to copy from somewhere in the sheet to somewhere else in the same sheet, ie

Sub RangeCopy()
With ActiveSheet
.Range("A1:I19").Copy Destination:=Destination.Range("D23")

End With
End Sub

But I can't work out how to make another workbook the destination!

What I need is to copy the range A315:I1500 from "Staff Planner" to A1 in "Planner". Generally both workbooks will be open although if I could also get it to open them, do the copy, save both and close them, this would be a bonus!

Any advice would be very much appreciated!

  VoG II 11:15 13 Jun 2008

This code goes in a regular module in Staff Planner. You have to open Staff Planner then run the macro. It assumes that Planner is in the same folder. After the copy it saves and closes Planner.xls but not Staff Planner.

Sub MyCopy()
Dim wb As Workbook
Set wb = Workbooks.Open("Planner.xls")
ThisWorkbook.Sheets("Plan").Range("A315:I1500").Copy Destination:=wb.Sheets("Plan").Range("A1")
Application.CutCopyMode = False
wb.Close savechanges:=True
End Sub

  VoG II 11:16 13 Jun 2008

This should be all on one line

ThisWorkbook.Sheets("Plan").Range("A315:I1500").Copy Destination:=wb.Sheets("Plan").Range("A1")

  montyburns 11:40 13 Jun 2008

Thanks Peter! You are a star!

I really couldn't get my head around it. I wasted three hours yesterday and came up with nothing, whereas you get this in a matter of minutes!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?