Error copying range to another Workbook

[Madmanz]

I get an error "Application-defined or object-defined error" when running this code. It works fine if copied to the same workbook.

The culprit is ("C" & Rows.Count). What am I doing wrong? Any help would be much appreciated, thanks.

               Range("AB1").Resize(1, 5).Copy
               Workbooks("Day Book").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats

 

[sanj_edu]

can you upload the excel sheet or mentioned the previous and end result you are expecting....

I get an error "Application-defined or object-defined error" when running this code. It works fine if copied to the same workbook.

The culprit is ("C" & Rows.Count). What am I doing wrong? Any help would be much appreciated, thanks.

               Range("AB1").Resize(1, 5).Copy
               Workbooks("Day Book").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats

 

[Madmanz]

Alright, I have 2 forms for work. An Order Form for writing up customer details, job description, etc. and a Day Book that lists every job taken on a check list.

My goal is to transfer all the important details from the Order Form (Order Number, Customer Name, etc) to the Day Book as soon as the Order Form is completed. But I don't want any entries to be overwritten.

 

[MARK858]

I get an error "Application-defined or object-defined error" when running this code. It works fine if copied to the same workbook.

What happens if you add the file extension (change xlsm to suit)?

Workbooks("Day Book[COLOR="#FF0000"].xlsm[/COLOR]").Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats

Is the workbook Day Book open?

What file extension are both workbooks?

 

[Madmanz]

The Order Form is a template file .xltm and the Day Book is .xls

Both workbooks are always open and adding the extension (.xls) gives me the same error.

 

[MARK858]

What happens with

Sub dddd()
    Range("AB1").Resize(1, 5).Copy
    With Workbooks("Day Book.xls").Sheets("Sheet1")
        .Range("C" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
    End With
End Sub

 

[Madmanz]

Ok got it working, thanks Mark. You got me thinking about file extensions and got me wondering what would happen if I change the file to .xlsm

 

[MARK858]

Did you test the code I posted (with and without the .xls in the code) before you changed it to xlsm?

 

[Madmanz]

Oh wow, I just tried your code in a .xls file and it worked, i didn't realise that the part in bold was meant to refer to the outside workbook as well, thanks a ton

What happens with

Sub dddd()
    Range("AB1").Resize(1, 5).Copy
    With Workbooks("Day Book.xls").Sheets("Sheet1")
        .Range("C" & [B].Rows.Count[/B]).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
    End With
End Sub

 

[MARK858]

Oh wow, I just tried your code in a .xls file and it worked, i didn't realise that the part in bold was meant to refer to the outside workbook as well, thanks a ton

It doesn't need to if they are the same file type, or more accurately if you aren't mixing old and new file types.

What your code was doing was looking at the row count on the active sheet which because it was a xlsm has a row count of 1,048,576.

Xls only has 65,536 rows and so it was screaming Help! I don't have that many rows.