Paste area Error on my macro
 

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.

Paste area Error on my macro
 

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"

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.

Paste area Error on my macro
 

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.

Paste area Error on my macro
 

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"
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.