One more try...no solution now for a month

[Guest]

Sub m01_GetData()
'
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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False


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

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



Having trouble with the Copy/destination line.....but only on this ONE
worksheet.. It works with all the others. The error I get is that paste area
is different shape. ...but the destination is only one cell!

Please HELP!!
Thanks

 

[Tom Ogilvy]

can you perform the operation manually using the same sheets/areas?

 

[macropod]

Hi justme,

Any merged cells involved?

Cheers

--
macropod
[MVP - Microsoft Word]


| Sub m01_GetData()
| '
| 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
|
| ViewMode = ActiveWindow.View
| ActiveWindow.View = xlNormalView
| ActiveSheet.DisplayPageBreaks = False
|
|
| firstRow = ActiveSheet.UsedRange.Cells(1).Row
| lastRow = ActiveSheet.UsedRange.Rows.Count + firstRow - 1
|
| 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
|
|
|
| Having trouble with the Copy/destination line.....but only on this ONE
| worksheet.. It works with all the others. The error I get is that paste
area
| is different shape. ...but the destination is only one cell!
|
| Please HELP!!
| Thanks
|

 

[Guest]

Actually, I'm not sure how to find out if there are merged cells. Obviously,
if I highlight the whole page and unmerge, it will unmerge any merged cells,
but out of 10k rows, I'm not sure how to identify merged cells. Actually, I
could just try an unmerge and try my macro again. But how can I unmerge
programmatically?

Thanks!

 

[Guest]

I thought I was sure I was able to do it manually before...that's why I was
so stumped, but I just tried it again and I couldn't. I tried selecting the
whole sheet & unmerging and it worked! but I would like to know what I'm
unmerging, so how can I identify unmerge cells (just this once, since she
just keeps updating the same spreadsheet each week), and then put unmerge-all
code into my sub?

Thanks so much!!!

m-

 

[okrob]

You could just select all the cells on the worksheet and look at the
cells format. If the checkbox for merge cells is checked (washed out,
but checked) then there are some merged cells on the sheet.
To unmerge them, you can just check the box and then uncheck it.
or progammatically:

Cells.UnMerge

this will unmerge any cells on the sheet.
or you can select the area you want to unmerge and then use

Selection.UnMerge

 

[Guest]

Ok, now I'm mad. I had unmerged the cells, saved the file, tried my macro on
it again, and it worked. ... but then I tried it again, and it didn't work.
Now I've figured something else out:

This macro is a loop. Each week I use it to copy info from 6 workbooks.
This one problem worksheet, since I unmerged all the cells in it, will work
if it is the FIRST workbook I copy, but not if I have already copied any of
the other worksheets. So, that is a strange problem. Do you have any ideas?
I'd still like to know exactly what I am unmerging.

Thanks!

 

[Guest]

Thanks for replying,

It is really confusing because the "merge cells" box was completely clear.
But I selected the whole sheet and clicked the unmerged button anyway and
saved it. After that, nothing should have changed, but I was able to get the
sub to work, so there must be something going on with merged cells. The sub
only works under certain conditions, though (see my above reply to Tom). I
need to identify merged cells so I can see what (if anything) I am unmerging.

Thanks

 

[okrob]

This will select and color all merged cells so you can find them...
Rob

Sub FindMergedCells()
Dim rng As Range
For Each cell In ActiveSheet.UsedRange.Cells
If cell.MergeCells Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next
rng.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

 

[Guest]

Hi Rob,

with your code I am getting run-time error 91, object variable or with block
variable not set at "rng.Select" line.

Also, do you have any idea why my sub would work on that one file if it is
the first one but not if it isn't the first one?

Thanks!

 

[okrob]

No idea... both subs work for me, although yours confuses me slightly.
I tried yours with about 5 files. you code dims both INTLraw and
DOMraw, but you only coded DOMraw transfer. Are you repeating this
sequence for another set of files for the INTLraw Seemed to me that
was the intent...
Anyway, sorry, I couldn't be more help. Like I said. They work on my
end without flaw and no matter what order I opened the files.
Rob

 

[Guest]

Thanks so much for your time, Rob.

Yes, I only posted the first part of the sub. The second does the same thing
for INTLraw.

I know that there is something strange with that one file I get. I just can
not figure out what it is.

Thanks again. Have a great weekend!

 

[Guest]

I think I solved it by using copy / destination instead of copy/paste!