copy and pasty data between workbooks

OfficeDummy · #1 (**permalink**) 02-18-08, 07:43

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

MikeTheBike · #2 (**permalink**) 02-18-08, 08:26

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

OfficeDummy · #3 (**permalink**) 02-18-08, 10:30

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

MikeTheBike · #4 (**permalink**) 02-18-08, 11:37

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

OfficeDummy · #5 (**permalink**) 02-18-08, 12:13

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?

OfficeDummy · #6 (**permalink**) 02-19-08, 08:11

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

Internet Services

Web Development

Digital Marketing

Consumer Tech