1

I have a working VBA script that copies exactly what I need except formatting. I at least need it to copy Fill and Text color.

Here is my current code.

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Cells.Select
Range("A1").Activate
Selection.ClearContents
Selection.UnMerge
Selection.ClearContents
Range("A1").Select
Workbooks.Open Filename:= _
    "Photo\Studio\\DAILY_REPORT_BACKUPS\DIGI_Review_Terry.xlsm"
Range("A1:XFD70").Select
Range("A1:XFD70").Activate
Selection.Copy
ActiveWindow.Close
Range("A1").Select
Sheets("Terry").Paste
ActiveWorkbook.Save

End Sub>
Community
  • 1
  • 1
Kyle Hinkebein
  • 73
  • 2
  • 9
  • I have tried adding .PasetSpecial but couldn't figure out how to make it work. – Kyle Hinkebein May 13 '15 at 15:18
  • Well if you manually did this, which paste special option works correctly for you? Perhaps "All except borders"? Or do you just want "All"? – chancea May 13 '15 at 15:22
  • I don't really know anything about paste special. I tried looking up how to do this and the only thing that resembled any of my code that I could try and copy was simply .PasteSpecial. I'm completely new to VBA but was able to arrive at the above code after much trial and error. I wouldn't know how to answer your question, sorry.. – Kyle Hinkebein May 13 '15 at 15:34
  • So try replacing `Sheets("Terry").Paste` with `Sheets("Terry").Range("A1").PasteSpecial Paste:=xlPasteAll` – chancea May 13 '15 at 16:11
  • Just gave that a shot and it didn't yield any different result. Still copied all cells but with no formatting, fill, or text color.. – Kyle Hinkebein May 13 '15 at 16:28
  • Oh I see, it is because you are closing it before you finish pasting the data over.. I can put together an answer I think – chancea May 13 '15 at 16:37

1 Answers1

1

The direct problem lies within the order of execution. Currently you are:

  1. Opening the workbook with the macro
  2. Opening the workbook with the data
  3. Copying the data
  4. Closing the workbook with the data
  5. Pasting the data into the workbook with the macro
  6. Saving the workbook with the macro (and now also with data)

The issue with this is that if you perform step 4 before step 5 the formatting is lost and only the text remains on the clipboard. So that means we have to switch steps 4 and 5 in the execution process.

In addition we should get rid of those select statements and work with the ranges directly. This is best practice and easier to understand in the code.

Here is the code with the steps marked in the order we want them to execute:

Private Sub Workbook_Open() 'step 1
Dim wb As Workbook
Dim wb2 As Workbook
Set wb = ActiveWorkbook

Application.DisplayAlerts = False
Cells.ClearContents
Cells.UnMerge
Cells.ClearContents

'step 2
Workbooks.Open Filename:= _
    "Photo\Studio\\DAILY_REPORT_BACKUPS\DIGI_Review_Terry.xlsm"
Set wb2 = ActiveWorkbook

'step 3
wb2.ActiveSheet.Range("A1:XFD70").Copy

'step 5 (switch with 4)
wb.Sheets("Terry").Range("A1").PasteSpecial Paste:=xlPasteAll

'step 4 (switch with 5)
wb2.Close

'step 6
wb.Save
End Sub

Here is the same code without the crazy step comments:

Private Sub Workbook_Open()
Dim wb As Workbook
Dim wb2 As Workbook
Set wb = ActiveWorkbook

Application.DisplayAlerts = False
Cells.ClearContents
Cells.UnMerge
Cells.ClearContents

Workbooks.Open Filename:= _
    "Photo\Studio\\DAILY_REPORT_BACKUPS\DIGI_Review_Terry.xlsm"
Set wb2 = ActiveWorkbook

wb2.ActiveSheet.Range("A1:XFD70").Copy
wb.Sheets("Terry").Range("A1").PasteSpecial Paste:=xlPasteAll
wb2.Close
wb.Save

End Sub
chancea
  • 5,858
  • 3
  • 29
  • 39
  • I'm trying this, and when I read down it, it does make more sense than what I had before but I'm still having trouble. When I run it, it opens my other document then says "Run-time error '438': Object doesn't support this property or method" it stops after that. – Kyle Hinkebein May 13 '15 at 18:28
  • @KyleHinkebein Do you know which line is causing that? – chancea May 13 '15 at 18:32
  • That's literally all the message says. It doesn't give a line, it doesn't give the option to debug and highlight in yellow, it just gives that message.. I tried slowly replacing the pieces of mine with yours and everything up to step 3 still worked. – Kyle Hinkebein May 13 '15 at 19:10
  • @KyleHinkebein What version of excel do you have? – chancea May 13 '15 at 19:15
  • It's 2013, I actually may of just gotten it to work blending the two of our codes together. Need to run it a few more times to make sure. – Kyle Hinkebein May 13 '15 at 19:29
  • Never mind, it must of been a fluke. It opened up, copied everything perfectly, pasted it in the new sheet and shut down. It won't do it again though. I used the first part of yours up to step 3 then just took out the string that closes the active window on mine.. – Kyle Hinkebein May 13 '15 at 19:55
  • @KyleHinkebein that is really bizarre... I can't think of why that would be happening. It works each time I run it :( – chancea May 13 '15 at 20:01
  • I'll have to keep playing around with it until I can figure out which part is messing up. Thank you very much for your help, I'm sure it's right there, just needs some tweaking.. – Kyle Hinkebein May 14 '15 at 12:46
  • 1
    Got it working. Just had to read through it and follow each process. Changed one or two things and it works great now. Thanks again @Chancea. – Kyle Hinkebein May 14 '15 at 14:36