Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
can you perform the operation manually using the same sheets/areas?
-- Regards, Tom Ogilvy "justme" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
Hi justme,
Any merged cells involved? Cheers -- macropod [MVP - Microsoft Word] "justme" wrote in message ... | 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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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 On Jan 25, 7:32 pm, justme wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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 On Jan 26, 12:43 pm, justme wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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 On Jan 26, 8:06 pm, justme wrote: 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
One more try...no solution now for a month
I think I solved it by using copy / destination instead of copy/paste!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Month function yields incorrect solution | Excel Worksheet Functions | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
copy worksheet from previous month and rename to current month | Excel Programming | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |