Paste area Error on my macro

[Guest]

This macro worked fine for many weeks. It prompts me to open a file, gets the
data from it and pastes it into my current blank workbook. Then it prompts
me for the next file and so on.

Then this week I got an error when I opened one of the excel files. It read:
"Run-time Error '1004':
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape..."


So, I looked at that particular excel file, and found that the person who
sent it to me had hidden one of the columns, so I added a line to the macro
to unhide any columns.

The macro successfully unhides the columns, but I still get the same error.

The file comes to me in page break view, but so do the others, and i have no
problem wit them.

One more thing to note: I can run the macro successfully on this file if it
is the first one I open and the first data to be pasted into my empty
workbook. However, if any other file is pasted into the empty workbook
first, it fails.





' GetData Macro
' Macro recorded 12/11/2006 by marib
'
Dim origin As String
Dim orgn As Workbook, dest As Workbook
Dim wsIr As Worksheet
Dim wsDr As Worksheet

Dim firstRow As Long
Dim lastRow As Long

Set wsIr = Sheets("INTLraw")
Set wsDr = Sheets("DOMraw")

Do

wsDr.Activate
Range("A1").Select
Application.ScreenUpdating = False
origin = Application.GetOpenFilename("Microsoft Office Excel
Files(*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw),*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw")
If origin = "False" Then Exit Sub
Workbooks.Open origin, 0, True
Set orgn = ActiveWorkbook
orgn.Activate
Sheets(1).Activate

firstRow = ActiveSheet.UsedRange.Cells(1).Row
lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1

With ActiveSheet
..DisplayPageBreaks = False
End With

With orgn.Sheets(1)
..Columns("A:Z").EntireColumn.Hidden = False
lastRow = wsDr.Cells(Rows.Count, "J").End(xlUp).Row
Set myRange = wsDr.Cells(lastRow, "J").Offset(1, -9)
..Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
Destination:=myRange
End With


Do you have any ideas for me?

Thanks.

 

[Jim Cone]

re: Set myRange = wsDr.Cells(lastRow, "J").Offset(1, -9)
.Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=myRange

With just a quick look...
The two ranges are not being sized in the same manor.
For one range, you are using the last cell in Column J (offset 9 rows)
to determine the last row.
In the other range you are letting Excel pick the last row using xlCellTypeLastCell.

You should be able to make it work using ...
.Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=myRange(1, 1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"justme"
<[email protected]>
wrote in message
data from it and pastes it into my current blank workbook. Then it prompts
me for the next file and so on.

Then this week I got an error when I opened one of the excel files. It read:
"Run-time Error '1004':
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape..."

So, I looked at that particular excel file, and found that the person who
sent it to me had hidden one of the columns, so I added a line to the macro
to unhide any columns.
The macro successfully unhides the columns, but I still get the same error.
The file comes to me in page break view, but so do the others, and i have no
problem wit them.
One more thing to note: I can run the macro successfully on this file if it
is the first one I open and the first data to be pasted into my empty
workbook. However, if any other file is pasted into the empty workbook
first, it fails.


' GetData Macro
' Macro recorded 12/11/2006 by marib
'
Dim origin As String
Dim orgn As Workbook, dest As Workbook
Dim wsIr As Worksheet
Dim wsDr As Worksheet
Dim firstRow As Long
Dim lastRow As Long
Set wsIr = Sheets("INTLraw")
Set wsDr = Sheets("DOMraw")

Do

wsDr.Activate
Range("A1").Select
Application.ScreenUpdating = False
origin = Application.GetOpenFilename("Microsoft Office Excel
Files(*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw),*.xl*;*.xls;*.xla;*.xlm;*.xlc;*.xlw")
If origin = "False" Then Exit Sub
Workbooks.Open origin, 0, True
Set orgn = ActiveWorkbook
orgn.Activate
Sheets(1).Activate

firstRow = ActiveSheet.UsedRange.Cells(1).Row
lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1

With ActiveSheet
..DisplayPageBreaks = False
End With

With orgn.Sheets(1)
..Columns("A:Z").EntireColumn.Hidden = False
lastRow = wsDr.Cells(Rows.Count, "J").End(xlUp).Row
Set myRange = wsDr.Cells(lastRow, "J").Offset(1, -9)
..Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
Destination:=myRange
End With

Do you have any ideas for me?
Thanks.

 

[Guest]

Hi Jim!

Thank you for replying.

What I am doing is copying everything on sheet Orgn (hence, the range from
A1 all the way to the LastCell).

Then I am coming back to my wsI or wsD sheet and selecting the cell in
Column A in the row following the last used cell in Column J. I can not
paste to cell 1,1 because this macro loops and needs to find the first
available row, based on trusting that Column J will never be empty.

so, there is really no shape of any range of cells on the destination sheet
to be in conflict with the shape of the range I am copying, as it is only
selecting one cell. Also, it works fine for all the other files, which are
basically structured the same. As I said, this worked up until they hid the
column this week. Do you have any other ideas?

Thank you for your time.

 

[Jim Cone]

Have you run out of columns or rows?
Is the line continuation character, following "Copy" also missing in your code?

--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"justme" <[email protected]>
wrote in message

Hi Jim!
Thank you for replying.
What I am doing is copying everything on sheet Orgn (hence, the range from
A1 all the way to the LastCell).
Then I am coming back to my wsI or wsD sheet and selecting the cell in
Column A in the row following the last used cell in Column J. I can not
paste to cell 1,1 because this macro loops and needs to find the first
available row, based on trusting that Column J will never be empty.

so, there is really no shape of any range of cells on the destination sheet
to be in conflict with the shape of the range I am copying, as it is only
selecting one cell. Also, it works fine for all the other files, which are
basically structured the same. As I said, this worked up until they hid the
column this week. Do you have any other ideas?
Thank you for your time.