[RESOLVED] error when pasting to a new workbook

[scoobster]

Hi

I have a routine that copies filtered records from a worksheet into a new workbook. Everything was working fine and then it stopped and I don't see why. Error is '438 0 object doesn't support this property or method'.

The error is happening on the line;

rng.Columns("C:H").SpecialCells(xlCellTypeVisible).Copy Destination:=newWorkBook.Sheet1.Range("A1")

The new workbook opens but then the error comes. I checked and there is no paste option so nothing is on the clipboard.

Full code is below;

Dim wbSaveAs As String
Dim wbSaveName As String
Dim newWorkBook As Workbook
Dim rng As Range

wbSaveName = Sheet4.Range("F4").Value
wbSaveAs = Sheet4.Range("F3").Value & wbSaveName

Set rng = Sheet1.ListObjects("PickList").AutoFilter.Range

     With Sheet1.Range("Picklist[#All]")
        
        Sheet1.ListObjects("PickList").Range.advancedfilter xlFilterInPlace, CriteriaRange:=Sheet4.Range("H1:J3"), Unique:=False
        
        Sheet1.Unprotect
        If rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
            
            If WorkbookIsOpen(wbSaveName) Then
                MsgBox "The workbook " & wbSaveAs & " is already open." & vbCr & vbCr & "Please close it and click Export once more.", vbInformation + vbDefaultButton1 + vbOKOnly, "Export"

' check if filters are active for the PICKLIST range and if not then set then on so that all records are
' displayed otherwise show all records anyway

                Exit Sub
            End If

            lblLastExport.Caption = "Creating Workbook"
            
            Set newWorkBook = Workbooks.Add
            With newWorkBook
            
                lblLastExport.Caption = "Copying Rows to New Workbook"
 
' ERROR is happening on this line           
                rng.Columns("C:H").SpecialCells(xlCellTypeVisible).Copy Destination:=newWorkBook.Sheet1.Range("A1")
               
                lblLastExport.Caption = "Finished Copying Workbook"
                
                .SaveAs Filename:=wbSaveAs, FileFormat:=xlNormal, CreateBackup:=False
                .Close Savechanges:=False
       
                lblLastExport.Caption = "Workbook Created"
            
            End With
   
        End If

Do I need to fully qualify the original workbook because the new workbook is open? If so, how to do that?

Thanks

[Ecniv]

Do I need to fully qualify the original workbook because the new workbook is open?

sounds like it.



You should be holding variable references to the current and new workbooks/sheets.

to do this - in the original workbook/code:

dim wrk as workbook, sht as worksheet
dim wrkDest as workbook, shtDest as worksheet

set wrk = activeworkbook
set sht = wrk.sheets("<name of your sheet>")

set wrkdest = application.workbooks.add
set shtdest = wrkdest.sheets(1)

then you can copy from the source to the destination
something like :

rng.Columns("C:H").SpecialCells(xlCellTypeVisible).Copy
shtDest.Range("A1").pastespecial xlPastevalues 'or whatever...?

Just remember to release at the end of your sub...

set wrk=nothing 'etc

[techgnome]

I don't think that you need to fully qualify the new workbook or the old one... the references you have should be fine. The reason that the paste option isn't on is because you gave it a destination to copy to... if you omit that, then it'll copy to the clipboard. Because you're going from one work book to another, you might need to copy to the clipboard, then do a paste into the other.... *shrug*.

-tg

[Siddharth Rout]

> Set rng = Sheet1.ListObjects("PickList").AutoFilter.Range

Can you paste this line of the code after the above line and tell me what do you get?

Msgbox rng.Address

[scoobster]

All working fine now so thanks for the input.

Working code to copy the value of columns C to K onto cell A1 of the newly created workbook is as follows;

                rng.Columns("C:K").SpecialCells(xlCellTypeVisible).Copy
                shtDest.Range("A1").PasteSpecial xlPasteValues

[Siddharth Rout]

Now that we've helped you, you can help us by marking the thread as resolved. If you have JavaScript enabled you can do that by selecting the Mark Thread Resolved item from the Thread Tools menu. Otherwise please insert "[Resolved]" at the start of the Subject and select the green checkmark from the Post Icons.

If someone has been particularly helpful you also have the ability to affect their forum 'reputation' by rating their post. Only those ratings that you give after you have 20 posts will actually count, but everyone you rate will know that you appreciate their help.