J
justme
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.
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.