Still Stumped
 

Hi, have not received a solution and still can not figure this one out.
When I open this macro from my blank template, it prompts me to open another
file, it gets the data from it and pastes it into my current blank workbook.
Then it prompts me for the next file, copies all the data and pastes the data
into the first column of the first blank row, according to row J. Then it
prompts for the next file and so on.

It works for every file I open except this ONE file, and I can not figure
out why. The error reads:
"Run-time Error '1004':
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape. Try one of the following:
- Click a single cell and then paste it....etc"

I can't figure it out, because the range I am specifying to paste to IS a
single cell (isn't it?).


Here is the code:

' 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


Like I said, it works for any excel sheet except this one workbook. the
worksheet comes to me with column "A" hidden. That's why I have the unhide
line in there.

Any ideas would be appreciated.
Thank you!

Still Stumped
 

Apparently, this range:

Set myRange = wsDr.Cells(lastRow, "J").Offset(1,-9)

is not as large as this one:

..Range("A1",.Cells.SpecialCells(xlCellTypeLastCel l)).Copy

I suggest stepping through the code with the
two relavent sheets open and watch the selection
area for the copy, then see where the destination
cell in column "A" is on the destination sheet and
if it is a row or more lower than the top left cell of the
"from" range. That's the only reason I can see. If it is
you will have to modify the destination sheet to delete some
rows so it will equal or be greater than the from sheet.


"justme" wrote:

Hi, have not received a solution and still can not figure this one out.
When I open this macro from my blank template, it prompts me to open another
file, it gets the data from it and pastes it into my current blank workbook.
Then it prompts me for the next file, copies all the data and pastes the data
into the first column of the first blank row, according to row J. Then it
prompts for the next file and so on.

It works for every file I open except this ONE file, and I can not figure
out why. The error reads:
"Run-time Error '1004':
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape. Try one of the following:
- Click a single cell and then paste it....etc"

I can't figure it out, because the range I am specifying to paste to IS a
single cell (isn't it?).


Here is the code:

' 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


Like I said, it works for any excel sheet except this one workbook. the
worksheet comes to me with column "A" hidden. That's why I have the unhide
line in there.

Any ideas would be appreciated.
Thank you!

Still Stumped
 

There may be some merged cells or some extra data some where.

Send me both files his and I will have a
look.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"justme" wrote:

Hi, have not received a solution and still can not figure this one out.
When I open this macro from my blank template, it prompts me to open another
file, it gets the data from it and pastes it into my current blank workbook.
Then it prompts me for the next file, copies all the data and pastes the data
into the first column of the first blank row, according to row J. Then it
prompts for the next file and so on.

It works for every file I open except this ONE file, and I can not figure
out why. The error reads:
"Run-time Error '1004':
The information cannot be pasted because the Copy area and the paste area
are not the same size and shape. Try one of the following:
- Click a single cell and then paste it....etc"

I can't figure it out, because the range I am specifying to paste to IS a
single cell (isn't it?).


Here is the code:

' 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


Like I said, it works for any excel sheet except this one workbook. the
worksheet comes to me with column "A" hidden. That's why I have the unhide
line in there.

Any ideas would be appreciated.
Thank you!