I've written a macro that is basically merging two documents sorta. Anyways
what I need is a command or something that will do the following:

Basically what i have is a list of names that have been subtotaled so each
name has 26 blank rows, what i need is when one row of information is
inserted under each persons name i need one of the 26 blank rows to be
deleted, therefore each person will only ever have 26 rows.

Is this possible???


Thanks
Mark

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Instead of Inserting Rows why not just write your information to one of
the blank rows?

Mark via OfficeKB.com wrote:
I've written a macro that is basically merging two documents sorta. Anyways
what I need is a command or something that will do the following:

Basically what i have is a list of names that have been subtotaled so each
name has 26 blank rows, what i need is when one row of information is
inserted under each persons name i need one of the 26 blank rows to be
deleted, therefore each person will only ever have 26 rows.

Is this possible???


Thanks
Mark

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Because in order to get the info I have to run a report from a unix based
enviroment and the report downloads into an excel workbook from there I
created a marco to pull the info that i need and send it into the final excel
report and to write the info in a blank row would take forever there are over
500 some people each one with different numbers.

bobbo wrote:
Instead of Inserting Rows why not just write your information to one of
the blank rows?

I've written a macro that is basically merging two documents sorta. Anyways
what I need is a command or something that will do the following:
[quoted text clipped - 12 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

I was implying you change your original code to, instead of inserting
rows, simply write the info to the blank rows already provided. Your
post is a little vague. What you mention can be done but all I can
offer is psuedo code based on your desricption. From what I gathered
from your post is that the unix numbers are inserted into a spreadsheet
that already has blank rows. You could use your macro to write them to
the blank rows. The code would be something like this

Workbooks("Final Rpt.xls").Worksheets("Sheet").Range("b1").value = _
Workbooks("Unixrpt.xls").Worksheets("Sheet1").rang e("a1").value

This would give the cell B1 the value of Sheet1 of the Final Report the
value of cell A! on Sheet1 of the Unix Report. If you could give some
more details I could probably help more.

Hinojosa via OfficeKB.com wrote:
Because in order to get the info I have to run a report from a unix based
enviroment and the report downloads into an excel workbook from there I
created a marco to pull the info that i need and send it into the final excel
report and to write the info in a blank row would take forever there are over
500 some people each one with different numbers.

bobbo wrote:
Instead of Inserting Rows why not just write your information to one of
the blank rows?

I've written a macro that is basically merging two documents sorta. Anyways
what I need is a command or something that will do the following:
[quoted text clipped - 12 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

Thats a good idea it just that not every bodies name stays in the same place.

heres how it works

Download the report from unix enviroment Downloads to ExcelI run the
macro which then seperates it into different columns(because when downloaded
its all in column A) then i have a name list on a personal worksheet so the
next macro refences that namelist and pulls that persons name and the time
(sick,regular,overtime etc.) and puts it into a diffrent sheetafter that it
send the information from the final sheet to a diffrent wrkbook.

In this workbook there are six different tabs each tab is named for a manager
so the last macro looks through all those until it finds the name its'
looking for it will then insert the row above the name. The only way i could
make the subtotals work is by inserting 26 blank rows so that it will read
all 26 rows and when the info is inserted each persons subtotal will update
and the groups that were created won't break. I created 26 lines for each
pay period so i was try to keep it some what pleaseant to the eye and not
have all these blank lines at the end of 26 weeks. I mean I guess I could
delete them one by one :( but let me know if you know any tricks to this
cause this is my first macro i have ever done I'm not an expert yet

bobbo wrote:
I was implying you change your original code to, instead of inserting
rows, simply write the info to the blank rows already provided. Your
post is a little vague. What you mention can be done but all I can
offer is psuedo code based on your desricption. From what I gathered
from your post is that the unix numbers are inserted into a spreadsheet
that already has blank rows. You could use your macro to write them to
the blank rows. The code would be something like this

Workbooks("Final Rpt.xls").Worksheets("Sheet").Range("b1").value = _
Workbooks("Unixrpt.xls").Worksheets("Sheet1").rang e("a1").value

This would give the cell B1 the value of Sheet1 of the Final Report the
value of cell A! on Sheet1 of the Unix Report. If you could give some
more details I could probably help more.

Because in order to get the info I have to run a report from a unix based
enviroment and the report downloads into an excel workbook from there I
[quoted text clipped - 14 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

I assume that you are using the find method to find the names in the
final spreadsheet. I will call the range variable returned by the find
method fnd in my code. I will also assume that you have picked up the
row that needs to be inserted. I will call this cpyrg in my code. I
will also use dest as a variable for the destination where I will paste
the row. I assume that your code goes something like this after the
name is found.

cpyrg.copy
fnd.insert(xldown)

I would do something like this

set dest = fnd.offset(-26,0)
' This makes dest the 26th row above the persons name.
If dest.value < "" then
set dest = dest.end(xldown).offset(1,0)
' If this is not the first entry on the spreadsheet for this person
then the end command finds
' the last continuous cell with a value that is not null. Then the
offset takes it down one
' more row to the first empty row.
end if

cpyrg.copy
dest.pastespecial

If my assumptions are correct then that should do it. The pastespecial
works just like paste and will not shift cells.



Hinojosa via OfficeKB.com wrote:
Thats a good idea it just that not every bodies name stays in the same place.

heres how it works

Download the report from unix enviroment Downloads to ExcelI run the
macro which then seperates it into different columns(because when downloaded
its all in column A) then i have a name list on a personal worksheet so the
next macro refences that namelist and pulls that persons name and the time
(sick,regular,overtime etc.) and puts it into a diffrent sheetafter that it
send the information from the final sheet to a diffrent wrkbook.

In this workbook there are six different tabs each tab is named for a manager
so the last macro looks through all those until it finds the name its'
looking for it will then insert the row above the name. The only way i could
make the subtotals work is by inserting 26 blank rows so that it will read
all 26 rows and when the info is inserted each persons subtotal will update
and the groups that were created won't break. I created 26 lines for each
pay period so i was try to keep it some what pleaseant to the eye and not
have all these blank lines at the end of 26 weeks. I mean I guess I could
delete them one by one :( but let me know if you know any tricks to this
cause this is my first macro i have ever done I'm not an expert yet

bobbo wrote:
I was implying you change your original code to, instead of inserting
rows, simply write the info to the blank rows already provided. Your
post is a little vague. What you mention can be done but all I can
offer is psuedo code based on your desricption. From what I gathered
from your post is that the unix numbers are inserted into a spreadsheet
that already has blank rows. You could use your macro to write them to
the blank rows. The code would be something like this

Workbooks("Final Rpt.xls").Worksheets("Sheet").Range("b1").value = _
Workbooks("Unixrpt.xls").Worksheets("Sheet1").rang e("a1").value

This would give the cell B1 the value of Sheet1 of the Final Report the
value of cell A! on Sheet1 of the Unix Report. If you could give some
more details I could probably help more.

Because in order to get the info I have to run a report from a unix based
enviroment and the report downloads into an excel workbook from there I
[quoted text clipped - 14 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200610/1