Hello all, and many thanks for previous help. Once again, I am stumped. I
have a worksheet that contains a report and subreport I am exporting from
Acess into Excel. The data in the report and subreport are variable
(sometimes more, sometimes less). I export the file into Excel, and the
subreport portion comes up off to the side below the report portion on the
same worksheet (i.e. the report fills up A1:I151, and the sub report fills up
J52:M64).

I've got enough wherewithal to figure out how to find the last cell with
data in it, and to find the cell containing unique data in the subreport to
select the 5 or so columns contained in it, just not able to string this all
together.

The issue here is that since it's all variable, today the report section may
end at row 151, and tomorrow it may go to row 250 or only row 50.
Additionally, I need to find the last cell of the report section (i.e.
A1:I151) with data in it, not the last row with data in it on the whole
sheet.

I need to be able to A)Find the unique data in the subreport to select the
data I need to cut, B)find the last row of the report with data populated in
it, C) skip 4 rows and paste the information cut from the subreport section.
I'd leave this all in Access, but it's going to a customer who has to have it
in Excel.

Any help would be most appreciated.
--
Best Regards,

Beth

--
Best Regards,

Beth

Hi,
The last row of the report, I think this will find that:
Sub Macro2()
Range("A1").Select
Selection.End(xlDown).Select
ReportLastRow = Activecell.Row
End Sub
This should put you on the last row.
What makes data unique in the subreport? Skip 4 rows from the bottom of the
"main" report? This would be (ReportLastRow +5).

Thanks,

"BethB" wrote:

Hello all, and many thanks for previous help. Once again, I am stumped. I
have a worksheet that contains a report and subreport I am exporting from
Acess into Excel. The data in the report and subreport are variable
(sometimes more, sometimes less). I export the file into Excel, and the
subreport portion comes up off to the side below the report portion on the
same worksheet (i.e. the report fills up A1:I151, and the sub report fills up
J52:M64).

I've got enough wherewithal to figure out how to find the last cell with
data in it, and to find the cell containing unique data in the subreport to
select the 5 or so columns contained in it, just not able to string this all
together.

The issue here is that since it's all variable, today the report section may
end at row 151, and tomorrow it may go to row 250 or only row 50.
Additionally, I need to find the last cell of the report section (i.e.
A1:I151) with data in it, not the last row with data in it on the whole
sheet.

I need to be able to A)Find the unique data in the subreport to select the
data I need to cut, B)find the last row of the report with data populated in
it, C) skip 4 rows and paste the information cut from the subreport section.
I'd leave this all in Access, but it's going to a customer who has to have it
in Excel.

Any help would be most appreciated.
--
Best Regards,

Beth

--
Best Regards,

Beth

David -

Many thanks for the information. I didn't explain my data properly,
though. The data is only a report and subreport as far as Access is
concerned. Once it's all exported into Excel, there's nothing identifying it
as such, just a jumble of data. That's why I was using ctrl+F to find the
column heading "Notes" to find the data I need to copy and paste. The macro
will then need to take that cut data and append it 4 rows south of where the
previous set of data stops. Here's an example of what my worksheet looks like
(the letters above each are column references):
A B C D
Part Ship Date Need Date Comments
Widget 05/05/05 05/07/05 Shipped on time
Bobbin 04/30/05 04/25/05 Shipped late
E F
G H
Part Ship Date Need
Date Notes
Widget 05/05/05 05/07/05
Released
Bobbin 04/30/05 04/25/05
On Hold

The amount of data in it varies, though, from export to export. Is there
a way to at least get the macro to paste it in the row it's currently on, but
in column A? I could have the macro insert the needed rows between the two,
then.

--
Best Regards,

Beth


"David" wrote:

Hi,
The last row of the report, I think this will find that:
Sub Macro2()
Range("A1").Select
Selection.End(xlDown).Select
ReportLastRow = Activecell.Row
End Sub
This should put you on the last row.
What makes data unique in the subreport? Skip 4 rows from the bottom of the
"main" report? This would be (ReportLastRow +5).

Thanks,

"BethB" wrote:

Hello all, and many thanks for previous help. Once again, I am stumped. I
have a worksheet that contains a report and subreport I am exporting from
Acess into Excel. The data in the report and subreport are variable
(sometimes more, sometimes less). I export the file into Excel, and the
subreport portion comes up off to the side below the report portion on the
same worksheet (i.e. the report fills up A1:I151, and the sub report fills up
J52:M64).

I've got enough wherewithal to figure out how to find the last cell with
data in it, and to find the cell containing unique data in the subreport to
select the 5 or so columns contained in it, just not able to string this all
together.

The issue here is that since it's all variable, today the report section may
end at row 151, and tomorrow it may go to row 250 or only row 50.
Additionally, I need to find the last cell of the report section (i.e.
A1:I151) with data in it, not the last row with data in it on the whole
sheet.

I need to be able to A)Find the unique data in the subreport to select the
data I need to cut, B)find the last row of the report with data populated in
it, C) skip 4 rows and paste the information cut from the subreport section.
I'd leave this all in Access, but it's going to a customer who has to have it
in Excel.

Any help would be most appreciated.
--
Best Regards,

Beth

--
Best Regards,

Beth

Hi Beth,
I am totally lost. it maybe the only to understand this is with the actual
data in a file. I am sorry, but i just can't tell what you are trying to cut
and paste. When I look at it, the columns don't even seem to go in
consecutive order. If you find the word "notes," everything from there down
and across can be cut and pasted to another point?

Thanks,

"BethB" wrote:

David -

Many thanks for the information. I didn't explain my data properly,
though. The data is only a report and subreport as far as Access is
concerned. Once it's all exported into Excel, there's nothing identifying it
as such, just a jumble of data. That's why I was using ctrl+F to find the
column heading "Notes" to find the data I need to copy and paste. The macro
will then need to take that cut data and append it 4 rows south of where the
previous set of data stops. Here's an example of what my worksheet looks like
(the letters above each are column references):
A B C D
Part Ship Date Need Date Comments
Widget 05/05/05 05/07/05 Shipped on time
Bobbin 04/30/05 04/25/05 Shipped late
E F
G H
Part Ship Date Need
Date Notes
Widget 05/05/05 05/07/05
Released
Bobbin 04/30/05 04/25/05
On Hold

The amount of data in it varies, though, from export to export. Is there
a way to at least get the macro to paste it in the row it's currently on, but
in column A? I could have the macro insert the needed rows between the two,
then.

--
Best Regards,

Beth


"David" wrote:

Hi,
The last row of the report, I think this will find that:
Sub Macro2()
Range("A1").Select
Selection.End(xlDown).Select
ReportLastRow = Activecell.Row
End Sub
This should put you on the last row.
What makes data unique in the subreport? Skip 4 rows from the bottom of the
"main" report? This would be (ReportLastRow +5).

Thanks,

"BethB" wrote:

Hello all, and many thanks for previous help. Once again, I am stumped. I
have a worksheet that contains a report and subreport I am exporting from
Acess into Excel. The data in the report and subreport are variable
(sometimes more, sometimes less). I export the file into Excel, and the
subreport portion comes up off to the side below the report portion on the
same worksheet (i.e. the report fills up A1:I151, and the sub report fills up
J52:M64).

I've got enough wherewithal to figure out how to find the last cell with
data in it, and to find the cell containing unique data in the subreport to
select the 5 or so columns contained in it, just not able to string this all
together.

The issue here is that since it's all variable, today the report section may
end at row 151, and tomorrow it may go to row 250 or only row 50.
Additionally, I need to find the last cell of the report section (i.e.
A1:I151) with data in it, not the last row with data in it on the whole
sheet.

I need to be able to A)Find the unique data in the subreport to select the
data I need to cut, B)find the last row of the report with data populated in
it, C) skip 4 rows and paste the information cut from the subreport section.
I'd leave this all in Access, but it's going to a customer who has to have it
in Excel.

Any help would be most appreciated.
--
Best Regards,

Beth

--
Best Regards,

Beth

David -

Sorry - I see that after posting, my last got jumbled up. In answer to
your question, yes, once the column heading "Notes" has been found, all info
from that column, plus the previous 4 columns and anything in the rows below
can be cut. That part is working for me great. It's just that while the
column I'm pasting to will always be A, the row reference isn't static. Hope
this helps explain. Sorry for all the back and forth. Any ideas?
--
Best Regards,

Beth


"David" wrote:

Hi Beth,
I am totally lost. it maybe the only to understand this is with the actual
data in a file. I am sorry, but i just can't tell what you are trying to cut
and paste. When I look at it, the columns don't even seem to go in
consecutive order. If you find the word "notes," everything from there down
and across can be cut and pasted to another point?

Thanks,

"BethB" wrote:

David -

Many thanks for the information. I didn't explain my data properly,
though. The data is only a report and subreport as far as Access is
concerned. Once it's all exported into Excel, there's nothing identifying it
as such, just a jumble of data. That's why I was using ctrl+F to find the
column heading "Notes" to find the data I need to copy and paste. The macro
will then need to take that cut data and append it 4 rows south of where the
previous set of data stops. Here's an example of what my worksheet looks like
(the letters above each are column references):
A B C D
Part Ship Date Need Date Comments
Widget 05/05/05 05/07/05 Shipped on time
Bobbin 04/30/05 04/25/05 Shipped late
E F
G H
Part Ship Date Need
Date Notes
Widget 05/05/05 05/07/05
Released
Bobbin 04/30/05 04/25/05
On Hold

The amount of data in it varies, though, from export to export. Is there
a way to at least get the macro to paste it in the row it's currently on, but
in column A? I could have the macro insert the needed rows between the two,
then.

--
Best Regards,

Beth


"David" wrote:

Hi,
The last row of the report, I think this will find that:
Sub Macro2()
Range("A1").Select
Selection.End(xlDown).Select
ReportLastRow = Activecell.Row
End Sub
This should put you on the last row.
What makes data unique in the subreport? Skip 4 rows from the bottom of the
"main" report? This would be (ReportLastRow +5).

Thanks,

"BethB" wrote:

Hello all, and many thanks for previous help. Once again, I am stumped. I
have a worksheet that contains a report and subreport I am exporting from
Acess into Excel. The data in the report and subreport are variable
(sometimes more, sometimes less). I export the file into Excel, and the
subreport portion comes up off to the side below the report portion on the
same worksheet (i.e. the report fills up A1:I151, and the sub report fills up
J52:M64).

I've got enough wherewithal to figure out how to find the last cell with
data in it, and to find the cell containing unique data in the subreport to
select the 5 or so columns contained in it, just not able to string this all
together.

The issue here is that since it's all variable, today the report section may
end at row 151, and tomorrow it may go to row 250 or only row 50.
Additionally, I need to find the last cell of the report section (i.e.
A1:I151) with data in it, not the last row with data in it on the whole
sheet.

I need to be able to A)Find the unique data in the subreport to select the
data I need to cut, B)find the last row of the report with data populated in
it, C) skip 4 rows and paste the information cut from the subreport section.
I'd leave this all in Access, but it's going to a customer who has to have it
in Excel.

Any help would be most appreciated.
--
Best Regards,

Beth

--
Best Regards,

Beth

Here is what I understand:

Export from Access to Excel

Main Report
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
(number of rows will vary)

Sub Report
xxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx

Questions
1. Where do you want to go (e.g. first line sub report?)
2. Where is the data that you want to cut and paste? (sub report?)
3. You want to cut and paste the data 4 rows south of sub report?

Dave
http://www.buyeranalytics.com
http://www.purchasingblogs.co

--
BuyerAnalytic
-----------------------------------------------------------------------
BuyerAnalytics's Profile: http://www.excelforum.com/member.php...fo&userid=2378
View this thread: http://www.excelforum.com/showthread.php?threadid=37443

There are 2 approaches I can think of...

First Approach - Variable Named Range:
With this approach you should have 2 sheets in your workbook. The
first sheet would be for the data from Access and the 2nd sheet would
be the report.

Since the number of rows in the first sheet will not be constant, I
would create a named range that automatically adjusts based upon the
size of the table. Here is the code:
=OFFSET('Sheet1'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),C OUNTA('Sheet1'!$1:$1))

To do this,
insert
name
define
Type the name of your range, then paste the above code in the "refers
to" box. You now have a variable named range that you can use in a
pivot table or chart.

Second Approach - Use a find last row function:

Function FindLastRow()
Dim r As Long
r = 1

Do While r < 65536 And Len(Worksheets("Sheet1").Cells(r, 2).Text) 0
r = r + 1
Loop

FindLastRow = r
End Function

This function returns the last row in the table. Once you have the
last row you could do something like this:

Dim row as Long
row = FindLastRow
Worksheets("Sheet1").Cells(row + 4, 1).Select
'you are now 4 rows below the last row in the first column.

Good Luck

Dave
http://www.buyeranalytics.com
http://www.purchasingblogs.com


--
BuyerAnalytics
------------------------------------------------------------------------
BuyerAnalytics's Profile: http://www.excelforum.com/member.php...o&userid=23783
View this thread: http://www.excelforum.com/showthread...hreadid=374436

Thanks much! Will give this a try.
--
Best Regards,

Beth


"BuyerAnalytics" wrote:


There are 2 approaches I can think of...

First Approach - Variable Named Range:
With this approach you should have 2 sheets in your workbook. The
first sheet would be for the data from Access and the 2nd sheet would
be the report.

Since the number of rows in the first sheet will not be constant, I
would create a named range that automatically adjusts based upon the
size of the table. Here is the code:
=OFFSET('Sheet1'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),C OUNTA('Sheet1'!$1:$1))

To do this,
insert
name
define
Type the name of your range, then paste the above code in the "refers
to" box. You now have a variable named range that you can use in a
pivot table or chart.

Second Approach - Use a find last row function:

Function FindLastRow()
Dim r As Long
r = 1

Do While r < 65536 And Len(Worksheets("Sheet1").Cells(r, 2).Text) 0
r = r + 1
Loop

FindLastRow = r
End Function

This function returns the last row in the table. Once you have the
last row you could do something like this:

Dim row as Long
row = FindLastRow
Worksheets("Sheet1").Cells(row + 4, 1).Select
'you are now 4 rows below the last row in the first column.

Good Luck

Dave
http://www.buyeranalytics.com
http://www.purchasingblogs.com


--
BuyerAnalytics
------------------------------------------------------------------------
BuyerAnalytics's Profile: http://www.excelforum.com/member.php...o&userid=23783
View this thread: http://www.excelforum.com/showthread...hreadid=374436

There is no need to use a loop to find the lastcell, the loop will just
slow the macro down. See some examples in
http://www.mvps.org/dmcritchie/excel...ars.htm#macros
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BethB" wrote in message ...
Thanks much! Will give this a try.