Problems with a Run-time error of 1004....

[CB]

Hello,

I'm trying to do something I thought was pretty simple, but guess not...

I am trying to copy an area in one file (input file) and then paste those
contents (insert cells) into a master file.

So, in the master file, I need the paste to insert however many lines are in
the buffer....

Anyhow, I'm getting the 1004 error. At work, as long as I am in a certain
cell of the master file, it worked, but at home, this version of Excel
doesn't ever work (2007 I think, it's got the ribbon thing so finding
anything is an extra 30 mins).

I've got to think there's a consistent way to insert the copied cells, but
if not, maybe there's another way to get that data into the master file?

thanks,
Chris

Here's the macro I am working with:



Sub Macro1()

'

'

' Macro1 Macro

' Macro recorded 12/23/2009 by Chris Powers

'

' What this doesn't address:

' 1) selecting file to import

' 2) separating expenses & deposits

' 3) determinng what column an expense should
go in

'

'

' I have this broken into multiple parts, otherwise I'll get a
1004 error every time.

'

' First part, open file, remove top 7 rows, get rid of
unneeded columns, save, close

'

'

'

Workbooks.OpenText Filename:="C:\TEMP\Dec Exp Tracker.TXT", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Rows("1:7").Select

Selection.Delete Shift:=xlUp

Columns("F:F").Select

Selection.Delete Shift:=xlToLeft

Columns("C").Select

Selection.Delete Shift:=xlToLeft

'

'

'

'

' Here I try to insert a column so this data lines up with Master file -
this always generates 1004 error.

'

' Range("A1").Select

' ActiveCell.FormulaR1C1 = "delete"

'

'

ActiveWorkbook.Save

ActiveWorkbook.Save

ActiveWorkbook.Close

'

'

' Second part, re-open file I just had open, select data, copy
it

'

' Move back to master file to it's current cell, paste data

'

Workbooks.Open Filename:="C:\TEMP\Dec Exp Tracker.TXT"

Rows("1:1").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Windows("C & A Tracker.xls(2)").Activate

Selection.Insert Shift:=xlDown

ActiveWindow.SmallScroll Down:=-21

End Sub

 

[JLGWhiz]

As far as I know, there is no difference between xl03 and sl07 for the copy
and paste feature. Same rules apply and VBA code works the same between the
two. Don't know for sure about Insert, but it should work the same. What
is the message you get with the 1004 error?

 

[OssieMac]

I am wondering if this is the same project for which I posted code for you on
Dec 24. At that time you said it was a csv file so maybe not; or perhaps with
the current problem of Microsoft Communities not sending notifications you
cannot find my reply.

Just in case, if you cannot find your reply, log into the newsgroup. If
after logging in you get an error message that the page cannot be displayed,
click the refresh button.

After logging in click the 'Edit my profile' button (just to the left of
Help and SignOut) and scroll down and click the link 'Show all my
notifications'. The most recent ones will be at the bottom. Click the link to
the required thread.

Anyway to answer your question here. You have not said which line is giving
you the error but I suspect it is Selection.Insert Shift:=xlDown.

You need to select a row where you want the rows inserted something like the
following.

Rows("12:12").Select
Selection.Insert Shift:=xlDown

At this point in time I will not attempt to confuse you be re-hashing your
recorded code but you should be aware that it can be coded better.

 

[CB]

Sorry, I didn't get any notifications, so I thought I hadn't posted earlier
(though I thought I did) and so tried what I could figure out.

It's OK, I know this isn't very good code. I'm sure there's much better
ways of doing what I'm trying to do. First time, in a long time, to do much
with macros, and back then it was just "How can we do X, Y, or Z?", not how
can we do it well... Now, I'd kind of like both, first get it working, then
improvements...

I can't imagine there's any good reason for having to modify the input file,
save it, close it and then open it again - that's just poor code, but at the
time, that was working, so I pressed on...

The problem that I've got is, and sorry if you answered this before, is
everything is variable. Both files (input and master) change constantly, so
it may have 15 lines today, 20 tomorrow). So, I can't see how to in the
master file to have it highlighting a row (unless I start the macro with that
row selected).

Yes, the error is with Selection.Insert Shift:=xlDown.

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
Select a rectangle that's the same size and shape, then paste

I just tried it by having the row in the Master file highlighted that I
wanted the paste to start inserting at, but no luck - same error. Same with
adding the line:
Rows("26:26").Select
to the macro.

thanks,
Chris

 

[JLGWhiz]

Yes, the error is with Selection.Insert Shift:=xlDown.

In your code you state that you are inserting a column. You cannot shift
down if you insert a column, only if you insert a row. With a column, you
must shift either xlShiftToRight . The defaults are xlShiftUp for rows and
xlShiftToLeft for columns.

Try Shift:=xlShiftToRight or just omit the shift and let the default shift
to left.

 

[JLGWhiz]

Sorry, I was thinking about deleting rows and columns. For insert there are
only the shift to right for columns and down for rows.

So you want Shift:=xlShiftToRight

 

[CB]

Interesting....what I wanted to do was insert rows.

So, is it how I'm doing the copy or the paste?

Here's the copy:
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy



Unfortunately, moving stuff to the right will break what's below as the rows
I'm inserting will get summed below, moving that below will shift things so
they won't add up correctly.

What, if anything, can I do in order to get it to be rows so I can move
things down?

thanks,
Chris

 

[news.microsoft.com]

If your sheet has been protected you may get a 1004 when you try to write to
it.
Try: sheets("xyz").activate
sheets("xyz").unprotect.