Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error: "the copy area & the paste area are not the same size & sh | Excel Discussion (Misc queries) | |||
Error handler if copy area different from paste area | Excel Programming | |||
Cut and Paste using Macro gives paste special method error | Excel Programming | |||
Macro to paste error | Excel Programming | |||
Macro to paste error | Excel Programming |