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:D").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

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?


"CB" wrote in message
...
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:D").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

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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

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

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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

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.


"CB" wrote in message
...
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

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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

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" wrote in message
...
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

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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

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

"JLGWhiz" wrote:

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" wrote in message
...
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

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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








.

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.


"CB" wrote in message
...
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

"JLGWhiz" wrote:

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" wrote in message
...
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

"OssieMac" wrote:

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.

--
Regards,

OssieMac


"CB" wrote:

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:D").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








.