Copy/Paste error

Topic

New Reply

Hello,

The segment of code below is giving me fits.

The macro is written to automatically open an existing spreadsheet, copy some cells and paste the data into the spreadsheet where the macro resides.

When I open the spreadsheet and run the macro, it runs exactly as I need without any issues.

However, if I run it a second time, the copying and pasting is all messed up. It seems to distribute the copied data across several additional columns, acting almost as a Text to Columns function.

I have to close out of Excel entirely to get it to run properly again. No issues with the first run, but subsequent runs I have problems.

[codebox]
Columns(“A:D”).Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close

Columns(“A:D”).Select
ActiveSheet.Paste[/codebox]

I tried modifications to the code to where it only selects and copies the data that is there:

[codebox] Range(“A1:D1”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
[/codebox]

I’ve also tried copying rows instead of columns.

And I have also selected in the target sheet just the range of A1.

I’ve set the display alerts back to “true” after closing, and I do get an alert about the size not matching. I suspect this might have something to do with it, but so far I’ve not been able to pinpoint exactly where the problem is.

The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.

I am using Excel 2007.

Thanks in advance for the advice!

Reply | Quote

Replies

Do you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.

Reply | Quote

Do you have merged cells in the source sheet or destination sheet? That is a known cause of problems when copying and pasting.

No, nothing is merged.

There is more code than what I’ve pasted, so I guess it’s possible that something elsewhere is causing the error. But this portion of the code is up near the top and the only thing before it is the opening of the other spreadsheet and the creation of a new worksheet.

I have also inserted at the beginning of the macro “Application.CutCopyMode = False” to clear out the clipboard.

Reply | Quote

I just noticed the sentence “The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.”. This means that the source and destination sheet don’t have the same number of rows. What happens if you copy a specific range, e.g.

Range(“A1:D1000”).Copy Destination:=ThisWorkbook.ActiveSheet.Range(“A1”)

(This instruction combines copy and paste)

Reply | Quote

I just noticed the sentence “The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.”. This means that the source and destination sheet don’t have the same number of rows. What happens if you copy a specific range, e.g.

Range(“A1:D1000”).Copy Destination:=ThisWorkbook.ActiveSheet.Range(“A1”)

(This instruction combines copy and paste)

I messed around with that a bit and I couldn’t get that to work. However, I may not have been doing it right. I’ll give it a shot and see what happens.

One question (and more info about what the macro is doing)…the spreadsheet that contains the data is not always named the same…it’s based on a selection made. The same selection also provide the information for the new spreadsheet, which is where the data will be pasted. Will that make any difference?

And also, how does the code know which “ThisWorkbook” to paste to? At this point in the code, the spreadsheet that has the data I want to copy is open and active.

Edited to add…Nevermind the above questions. It worked and I realize how it knew which workbook. It’s the workbook that contains the code. Thanks!

Thanks for being patient with me. I really do appreciate the assistance.

Reply | Quote