Macro to paste error

  • Thread starter Thread starter Lorenzo
  • Start date Start date
L

Lorenzo

I am trying to get a macro to work, but quite surprisingly what I
thought would have been the simplest part of the code just doesn't
want to work. I wrote a macro that
1. Selects a sheet in the workbook
2. Selects a range of cells and copies them (simple text, no formulae)
3. Opens an existing workbook
4. Creates a new sheet naming it with the current date

At this point it should paste the data in the clipboard onto the new
sheet, but instead I get this annoying "Runtime error 438 - Property
or method not supported by object" message, followed by the yellow
line of death over the last line of my code. I am quite new to VB, and
am a bit lost. Can anybody help?
Thank you for any suggestions.

Here's my code

Sub Copy_to_fix

Dim fix
fix = "G:\AREA GRUPPO...\3-5 fix.xls"
Sheets("last settori").Select
ActiveWorkbook.ActiveSheet.Range("B1:AU1200").Copy
Workbooks.Open filetoopen
Windows("3-5 fix.xls").Activate
ActiveWindow.WindowState = xlMaximized
Sheets.Add.Move after:=Sheets(Sheets.Count)
Sheets("Foglio1").Name = Format(Date, "DD MMM YY")
ActiveCell.Paste ***YELLOW LINE OF DEATH***

End sub
 
Derek, thanks a lot for your help, though I had already thought of
that solution, but unfortunately it wasn't that. I would get another
problem message with something slightly different (something like
"procedure not valid for this option".
I anyway managed to work my way around it. I had the macro copy and
paste after it had opened the workbook and create the new sheet.
Apparently it must have been losing the clipboard data somewhere in
the process. Macro is slightly longer (there's a couple of workbook
activate and sheets.select more), but it is not working brillantly.
Thank you very much anyway!!!
Lorenzo
 
Back
Top