Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Red face Unanswered: copy and paste data between workbooks

    Hello, everyone!

    I have to copy&paste data from one workbook to another on a monthly basis, and the code should transfer only mathcing data. I've got a hunch as to how this needs to be done...

    But at the first stage, I can even activate the source workbook.

    Here's the code snippet:

    Code:
    Dim strSheet As String
    Dim lSpalte
    Dim z, s
    Dim WBQuelle, WBZiel As Object
    
    
    strSheet = RefEdit2.Value
    lSpalte = RefEdit3.Value
    
    'source wb
    Set WBQuelle = "C:\Datenbank\Versuch November 2008.xls"
    'destination wb
    Set WBZiel = "C:\Datenbank\Versuch November 2007.xls"
    
    Workbooks(WBQuelle).Activate
    
    LetzteZeile = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    LetzteZeile1 = WBZiel.Sheets("Tabelle3").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    The path is correct, I also tried declaring WBQuelle and WBZiel as Workbook, but it didn't work as well - in either case I get the runtime error "Object required".

    Any suggestions?? Thanks a looooot in advance!

    Best,
    OfficeDummy
    Last edited by OfficeDummy; 02-18-08 at 11:36.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You are getting the Object Required error because
    "C:\Datenbank\Versuch November 2008.xls"
    isn't an object (it's a string!).

    You need something like this perhaps

    Code:
    Dim strSheet As String
    Dim lSpalte As String
    Dim z, s
    Dim WBQuelle as Workbook
    Dim WBZiel As Workbook
    
    
    strSheet = RefEdit2.Value
    lSpalte = RefEdit3.Value
    
    'source wb
    Workbooks.Open "C:\Datenbank\Versuch November 2008.xls"
    Set WBQuelle = ActiveWorkbook
    
    LetzteZeile = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    
    'destination wb
    Workbooks.Open "C:\Datenbank\Versuch November 2007.xls"
    Set WBZiel = ActiveWorkbook
    
    LetzteZeile1 = WBZiel.Sheets("Tabelle3").Range("A1").SpecialCells(xlCellTypeLastCell
    I assume this code is written in an Excel module !


    HTH

    You may also want to use
    Application.ScreenUpdating = False
    Application.ScreenUpdating = True
    etc.?

    MTB

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    Thanks for your rash help, Mike!

    Unfortunately I can't use ActiveWorkbook because it's too specific, but your reply pushed me into the right direction.

    To the task: my source table is a pivot table that calculates monthly costs which are sorted by cost centre row-wise and by cost description column-wise. The destination table has many sheets, each of them is named after a cost centre. I have to pull the right data from the source table and transport it to the destination table - right cost description into the right sheet (=cost centre)!
    Now my code is nearly complete, but I'm getting runtime error: types icompatible.

    Why???

    Here's the entire code ("glued" to the UserForm):

    Code:
    Dim strSheet As String
    Dim strKst As String
    Dim lSpalte
    Dim z, s, t
    Dim WBQuelle, WBZiel As Workbook
    
    'RefEdit Box on the UserForm, where user inputs the cost centre
    strSheet = RefEdit2.Value
    'and the column, where the costs should be imported into
    lSpalte = RefEdit3.Value
    
    'sets source WB
    Set WBQuelle = Workbooks("Versuch November 2008.xls")
    'and destination
    Set WBZiel = Workbooks("Versuch November 2007.xls")
    
    'calculates last row and last column:
    LetzteZeile = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    LetzteSpalte = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCells(xlCellTypeLastCell).Column
    LetzteZeile1 = WBZiel.Sheets("Tabelle3").Range("A1").SpecialCells(xlCellTypeLastCell).Row
    
    Sheets(strSheet).Activate
    
    If RefEdit3.Value = "a" Then
    
        lSpalte = 1
        
    ElseIf RefEdit3.Value = "b" Then
    
        lSpalte = 2
        
    ElseIf RefEdit3.Value = "c" Then
    
        lSpalte = 3
        
    Else
    
        lSpalte = 4
        
    End If
    
    
    'checks for matching data
    
    'check the destination row
    For s = 1 To LetzteZeile1
    
        'loops the source table, rows
        For z = 1 To LetzteZeile
        
            'now look for matches columnwise:
            For t = 1 To LetzteSpalte
        
                'the first row of the source table are cost centers; store them in the string
                'error comes here:
                strKst = WBQuelle.Sheets("Tabelle1").Cells(1, t).Value
           
                If WBQuelle.Sheets("Tabelle1").Cells(z, 1).Value = WBZiel.Sheets(strSheet).Cells(s, 1).Value Then
           
                    'determine the right cost centre:
                     If strKst = strSheet Then
        
                        WBQuelle.Sheets("Tabelle1").Cells(z, 4).Copy Destination:=WBZiel.Sheets(strSheet).Cells(s, lSpalte)
                
                        MsgBox "z = " & z & " s = " & s, vbOKCancel, Ausgabe
                
                    End If
             
            
                 End If
                 
            Next t
    
        Next z
    
    Next s
    Thanks in advance for your help!

    Best,
    OfficeDummy
    Last edited by OfficeDummy; 02-18-08 at 11:35.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    rash ?????

    It would help enormously if you could give us a hint where the error occurs??

    However, on the basis that both Workbooks have been opened presviously in some unseen code, perhaps these lines

    LetzteZeile = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCel ls(xlCellTypeLastCell).Row
    LetzteSpalte = WBQuelle.Sheets("Tabelle1").Range("A1").SpecialCel ls(xlCellTypeLastCell).Column
    LetzteZeile1 = WBZiel.Sheets("Tabelle3").Range("A1").SpecialCells (xlCellTypeLastCell).Row

    should be

    LetzteZeile = WBQuelle.Sheets("Tabelle1").Cells.SpecialCells(xlC ellTypeLastCell).Row
    LetzteSpalte = WBQuelle.Sheets("Tabelle1").Cells.SpecialCells(xlC ellTypeLastCell).Column
    LetzteZeile1 = WBZiel.Sheets("Tabelle3").Cells.SpecialCells(xlCel lTypeLastCell).Row

    ??


    MTB

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    Hi,

    I confused German and English... I meant to say quick reply.

    Actually, I did post the entire code and marked where error occurs. Is there a technical problem with my post?

  6. #6
    Join Date
    Jan 2008
    Posts
    74
    Okay, copy&paste works fine now. But... there's a small problem: the grid in the destination workbook disappears in cells where the data is being copied into.

    Why, and how can I avoid it? I like my grid.

    Thanks,
    OfficeDummy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •