PDA

View Full Version : Copy and paste of multiple ranges



koevet2
12-16-2008, 10:51 AM
Hello,
this is my first post here, great forum.
I have spent some time searching the forum for a solution to my problem, but I could't find any. I had some old Access related experience with VBA.

I have the following problem. I'm trying to copy to a separate excel file multiple ranges defined in a reference table:
eg.
FILE1 | A1:G3
FILE2 | A1:G1,A5:G7
FILE3 | a1:g1,a8:g9

etc.

My code looks like:

'' Loop on reference table...

Worksheets("Report").Range(C.Offset(0, 1)).Copy
...
Set MiXL = GetObject("", "Excel.Application")
Set WB = MiXL.Workbooks.Add
With WB.ActiveSheet.Cells("1", "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

When I open the generated files, the range intervals are not respected and FILE 2 (for instance) contains also the interval A2:G5 - that should not be in the specified range.
I believe that the problem lives in the way I define the intervals. I have also attached a sample excel file with the full macro and some sample data.

Thanks for your time

Luciano

Benzadeus
12-16-2008, 04:51 PM
That's weird. It appears that Excel simple makes a mistake when copying the range to the other workbook.

koevet2
12-17-2008, 02:57 AM
I think I have found a solution but is this an Excel 2003 bug?

Thanks
Luciano

Benzadeus
12-17-2008, 03:01 AM
I don't know. I tested the range before writing on the new workbook, I even switched range intervals and it was correct. But, when it tries to write on the new workbook, it makes a mistake.

This can be solved splitting the range information in the cell where you define the range area to write, of course, but I tought this weird.