Excel VBA copy/pastespecial to other workbook 1

[dmuroff]

Hi,

I am trying to copy and pastespecial (values) a range of cells through VBA from one workbook to another. Here is my code thus far:

Private Sub copypastedata()

storeno = 20
While storeno <= 20
    open2007currentstore = DLookup("location", "storelocation", "store=" & storeno)
    open2008currentstore = DLookup("newlocation", "storelocation", "store=" & storeno)
Set xl4obj = CreateObject("Excel.application")
 
   xl4obj.Workbooks.Open open2007currentstore
   xl4obj.Application.Visible = True
    
    xl4obj.Sheets("Sales Comparison").Activate
     xl4obj.range("a1:i361").Select
    xl4obj.Selection.Copy
    
    
'Set xl4obj = CreateObject("Excel.application")
Sleep (1000)
   xl4obj.Workbooks.Open open2008currentstore
   xl4obj.Application.Visible = True
    xl4obj.Sheets("Sales Comparison").Activate
     xl4obj.range("a1:i361").Select
     Sleep (5000)
    
    xl4obj.Selection.Pastespecial paste:=xlvalues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    xl4obj.Application.CutCopyMode = False
    xl4obj.DisplayAlerts = False
    
    xl4obj.Application.Quit
    Set xl4obj = Nothing
    Application.Quit
    storeno = storeno + 10
    Wend
    
End Sub

Both workbooks open fine and the open2007currentstore will copy the range perfectly. The problem exists in

 xl4obj.Selection.Pastespecial paste:=xlvalues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

where I keep getting a runtime "object required" error. I have tried a variety of alternatives activesheet.paste etc. but I am always arriving at the same error.

Any help would be greatly appreciated.

Happy Holidays!

 

[PHV]

What about this ?

storeno = 20
Set xl4obj = CreateObject("Excel.application")
xl4obj.Visible = True
While storeno <= 20
  open2007currentstore = DLookup("location", "storelocation", "store=" & storeno)
  open2008currentstore = DLookup("newlocation", "storelocation", "store=" & storeno)
  Set wb2007 = xl4obj.Workbooks.Open(open2007currentstore)
  wb2007.Sheets("Sales Comparison").Range("a1:i361").Copy
  Sleep (1000)
  Set wb2008 = xl4obj.Workbooks.Open(open2008currentstore)
  wb2008.Sheets("Sales Comparison").Range("a1:i361").Pastespecial paste:=-4163, Operation:=-4142, SkipBlanks:= _
        False, Transpose:=False
  xl4obj.CutCopyMode = False
  wb2008.Save
  wb2008.Close
  wb2007.Close
  storeno = storeno + 10
Wend
xl4obj.Quit
Set xl4obj = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmuroff]

Works like a charm! Thank you PHV!