VBA Lookup errors

**dontich** · 06-18-2012, 11:01 AM

I am writing a macro that needs to look up data in a different workbook in the first sheet with a particular name. I need to copy all the data from the sheet into a select worksheet in a select workbook.

I have the following code so far.

Sub CopyCells(smallname As String, sheetname As String, LocoName As String, cells As Range, Bookinto As String, Sheetinto As String)
    Workbooks.Open Filename:=LocoName
    Workbooks(smallname).Activate
    Worksheets(sheetname).Activate
    Range("A1:Z1000").Select
    ActiveSheet.Copy

    Workbooks(Bookinto).Activate 'activate book
    Worksheets(Sheetinto).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Workbooks(smallname).Close
End Sub

It works fine with debugging however when I try to copy the information from the files it does not copy but makes a new version of it called Book3. then it closes the original. this is fine but not really useful...
I am sure i am making a silly mistake can anyone find it?

**wallyeye** · 06-18-2012, 11:21 AM

What do the different parameters represent? It looks like you are referring to the active workbook/worksheet and the workbook/worksheet you are opening, there might be a better way to reference them.

Something like:

dim wbkLoco as workbook

set wbkloco = application.workbooks.open filename:=loconame
wbkloco.worksheets(SheetInto).Range("A1:F1000").value = workbooks(smallname).worksheets(Sheetname).range("A1:F1000").value
workbooks(smallname).close

set wbkloco = nothing

**dontich** · 06-18-2012, 11:31 AM

Originally Posted by wallyeye

What do the different parameters represent? It looks like you are referring to the active workbook/worksheet and the workbook/worksheet you are opening, there might be a better way to reference them.

Something like:

dim wbkLoco as workbook

set wbkloco = application.workbooks.open filename:=loconame
wbkloco.worksheets(SheetInto).Range("A1:F1000").value = workbooks(smallname).worksheets(Sheetname).range("A1:F1000").value
workbooks(smallname).close

set wbkloco = nothing

Hmm this does not work. The line
set wbkloco = application.workbooks.open filename:=loconame
returns an error that end of line is exspected, I think it is because I can not set a workbook when I am opening it?

**Leith Ross** · 06-18-2012, 11:21 AM

Hello dontich,

Welcome tot he Forum!

Try this revision of your macro and let me know the results.


Sub CopyCells(smallname As String, sheetname As String, LocoName As String, cells As Range, Bookinto As String, Sheetinto As String)

    Dim DstWkb As Workbook
    Dim DstWks As Worksheet
    
    Dim SrcWkb As Workbook
    Dim SrcWks As Worksheet

        Set SrcWkb = Workbooks(smallname)
        Set SrcWkb = SrcWkb.Worksheets(sheetname)
        
        Set DstWkb = Workbooks(Bookinto)
        Set DstWks = DstWkb.Worksheets(Sheetinto)
        
        SrcWks.Range("A1:Z1000").Copy Destination:=DstWks.Range("A1")
        
        SrcWkb.Close
        
End Sub

**dontich** · 06-18-2012, 11:32 AM

Originally Posted by Leith Ross

Hello dontich,

Welcome tot he Forum!

Try this revision of your macro and let me know the results.


Sub CopyCells(smallname As String, sheetname As String, LocoName As String, cells As Range, Bookinto As String, Sheetinto As String)

    Dim DstWkb As Workbook
    Dim DstWks As Worksheet
    
    Dim SrcWkb As Workbook
    Dim SrcWks As Worksheet

        Set SrcWkb = Workbooks(smallname)
        Set SrcWkb = SrcWkb.Worksheets(sheetname)
        
        Set DstWkb = Workbooks(Bookinto)
        Set DstWks = DstWkb.Worksheets(Sheetinto)
        
        SrcWks.Range("A1:Z1000").Copy Destination:=DstWks.Range("A1")
        
        SrcWkb.Close
        
End Sub

Hmm this would work if the other file is open beforehand I am having trouble adding that into your code

**dontich** · 06-18-2012, 11:33 AM

Also, Thanks alot guys!,, i know alot of excel formula stuff but am not amazing at VBA yet so I will try to post on the forums section and help other people out there.