I know I've seen tips on this here before, but I can't
find them now.

I have set up some web-queries that are on 6 sheets
in a workbook. (The source data is paginated; setting
up 6 queries, one for each page, was my solution.)

Now I want to work out a macro to combine the data from
the pages into a new workbook. I'd like to copy various
of the cells based on filtering criteria and edit others.

But we could start out more simply. Would someone be able
to get me going in the right direction with some sample
VBA that just cycles through the pages and finds and copies
the ranges of data into the new sheet? Would be most appreciated.

--
dman

http://www.rondebruin.nl/summary.htm



--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Dallman Ross" <dman@localhost. wrote in message
...
I know I've seen tips on this here before, but I can't
find them now.

I have set up some web-queries that are on 6 sheets
in a workbook. (The source data is paginated; setting
up 6 queries, one for each page, was my solution.)

Now I want to work out a macro to combine the data from
the pages into a new workbook. I'd like to copy various
of the cells based on filtering criteria and edit others.

But we could start out more simply. Would someone be able
to get me going in the right direction with some sample
VBA that just cycles through the pages and finds and copies
the ranges of data into the new sheet? Would be most appreciated.

--
dman

In , Peo Sjoblom
spake thusly:

http://www.rondebruin.nl/summary.htm

Excellent stuff there. Thanks. I'm trying it out.

Dallman

In , Dallman Ross <dman@localhost.
spake thusly:

In , Peo Sjoblom
spake thusly:

http://www.rondebruin.nl/summary.htm

Excellent stuff there. Thanks. I'm trying it out.

I find Ron de Bruin's stuff highly useful. I appreciated
Mr. Latham's code too. I am studying all of it.

I have to settle on something, and for now I am working through
Ron's code, but this one rather than the summary macro peo showed us:

http://www.rondebruin.nl/copy2.htm

I have stuff I don't care about on the first row, and
rows 2-3 are repeated headers. So I went with this part
from down lower on Ron's page I just cited:

Copy from row 2 till the last row with data

That is working. I've started on Row 4, where my data begins.
It worked well. But now I want to fix it up. Here are
my first 3 concerns:

1) I want to copy the header rows (2-3), but only from the first sheet
copied.

2) In Row 1 of the sheets is something potentially helpful to the macro:
it says:

Page 1 2 3 4 5

I have that row hidden on the sheets, but I would like the macro
to look there and find that "5" is the last sheet and not bother
trying to copy (empty, but for the header rows) sheets after that
in my workbook.

(Sometimes there are more or fewer pages to the data. I have
the queries set up for more than I expect to need, and the last
pages end up blank.)

Alternatively, we could not bother with that but just look to
make sure there is data on Row 4 (A4) and skip trying to copy
the sheet if there is not.


3) I don't want to copy rows that say "Canceled" in Column A.


Here's something else I added to the code already:

DestSh.UsedRange.Columns.WrapText = False 'dman
DestSh.UsedRange.Columns.AutoFit 'dman

I did that right after the loop through the workseehts closes. It
works as I'd hoped.

Thanks for any help here. Break it down -- if you can help
with any of (1), (2), or (3) I've listed, please post. I'd
very much appreciate it.

Dallman

P.S. Here is Ron's code that I'm using, except I'm copying from Row 4:

http://www.rondebruin.nl/copy2.htm excerpt:
---------------------------------------------
Copy from row 2 till the last row with data

Note: This example use the function LastRow
Important: Be sure that there is no empty sheet in your workbook

We can copy all cells on the sheet with this line:
sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

But what if we do not want to copy the same header row each time.
The example below will copy from row 2 till the last row with data on each sheet

Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

For the first request, copying from row 2 on first sheet, row 4 for the rest:

Add this to the variable declarations:
Dim sRow as Integer

then somewhere before the beginning of the For Each sh... loop add this
statement:
sRow = 2

Change the line that really does the work ( sh.Range(sh.Rows(2))...
to use sRow instead of 2:
sh.Range(sh.Rows(sRow))....
and right below that line of code add:
sRow=4

The first time the loop is run it will copy from row 2, and after that it
will always copy from row 4.

The code below includes those changes, plus it adds a test within the loop
to see if A4 is empty, and if it is empty, the copy is not performed. Your
3rd request, not to copy individual rows if they contain the word "Canceled"
in column A is a little more difficult since Ron's code (and even mine) is
copying a large area based on a start and end point and without regard to
what's in between. Probably best to add another routine to go to the
MergeSheet and delete rows that have Canceled in column A after all of the
work performed by the loop in this code is finished. Here's my modification
to your displayed code
Sub Test3()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim sRow As Integer ' jlatham

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If Not IsEmpty(sh.Range("A4")) Then 'jlatham
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(sRow), sh.Rows(shLast)).Copy _
DestSh.Cells(Last + 1, "A")
sRow = 4 ' jlatham
End If ' jlatham
End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

"Dallman Ross" wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

In , Peo Sjoblom
spake thusly:

http://www.rondebruin.nl/summary.htm

Excellent stuff there. Thanks. I'm trying it out.

I find Ron de Bruin's stuff highly useful. I appreciated
Mr. Latham's code too. I am studying all of it.

I have to settle on something, and for now I am working through
Ron's code, but this one rather than the summary macro peo showed us:

http://www.rondebruin.nl/copy2.htm

I have stuff I don't care about on the first row, and
rows 2-3 are repeated headers. So I went with this part
from down lower on Ron's page I just cited:

Copy from row 2 till the last row with data

That is working. I've started on Row 4, where my data begins.
It worked well. But now I want to fix it up. Here are
my first 3 concerns:

1) I want to copy the header rows (2-3), but only from the first sheet
copied.

2) In Row 1 of the sheets is something potentially helpful to the macro:
it says:

Page 1 2 3 4 5

I have that row hidden on the sheets, but I would like the macro
to look there and find that "5" is the last sheet and not bother
trying to copy (empty, but for the header rows) sheets after that
in my workbook.

(Sometimes there are more or fewer pages to the data. I have
the queries set up for more than I expect to need, and the last
pages end up blank.)

Alternatively, we could not bother with that but just look to
make sure there is data on Row 4 (A4) and skip trying to copy
the sheet if there is not.


3) I don't want to copy rows that say "Canceled" in Column A.


Here's something else I added to the code already:

DestSh.UsedRange.Columns.WrapText = False 'dman
DestSh.UsedRange.Columns.AutoFit 'dman

I did that right after the loop through the workseehts closes. It
works as I'd hoped.

Thanks for any help here. Break it down -- if you can help
with any of (1), (2), or (3) I've listed, please post. I'd
very much appreciate it.

Dallman

P.S. Here is Ron's code that I'm using, except I'm copying from Row 4:

http://www.rondebruin.nl/copy2.htm excerpt:
---------------------------------------------
Copy from row 2 till the last row with data

Note: This example use the function LastRow
Important: Be sure that there is no empty sheet in your workbook

We can copy all cells on the sheet with this line:
sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")

But what if we do not want to copy the same header row each time.
The example below will copy from row 2 till the last row with data on each sheet

Sub Test2()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy DestSh.Cells(Last + 1, "A")

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

In , JLatham
<HelpFrom @ jlathamsite.com.(removethis) spake thusly:

Great help! At first your code offered didn't work, and I was getting
frustrated. Then I realized you hadn't inserted the "sRow = 2" statement
in that you described before showing the code. I put it in, and it
all works just fine! Thank you.

Your 3rd request, not to copy individual rows if they contain
the word "Canceled" in column A is a little more difficult since
Ron's code (and even mine) is copying a large area based on a
start and end point and without regard to what's in between.
Probably best to add another routine to go to the MergeSheet and
delete rows that have Canceled in column A after all of the work
performed by the loop in this code is finished.

Understood. I'm going to work on this now. I hope I can use
the same 'DestSh.UsedRange' stuff that's in the code. Otherwise,
I'll have to figure it out with some trial and error.

Another thing is, I want to drop the formatting when I copy.
(Then I'll add formatting latter in an add-on macro.) How
can I do that?

Dallman


---------------------------------------------------------------------
For the first request, copying from row 2 on first sheet, row 4
for the rest:

Add this to the variable declarations:
Dim sRow as Integer

then somewhere before the beginning of the For Each sh... loop
add this statement:
sRow = 2

Change the line that really does the work ( sh.Range(sh.Rows(2))...
to use sRow instead of 2:
sh.Range(sh.Rows(sRow))....
and right below that line of code add:
sRow=4

The first time the loop is run it will copy from row 2, and after that it
will always copy from row 4.
The code below includes those changes, plus it adds a test within the loop
to see if A4 is empty, and if it is empty, the copy is not performed. Your
3rd request, not to copy individual rows if they contain the word "Canceled"
in column A is a little more difficult since Ron's code (and even mine) is
copying a large area based on a start and end point and without regard to
what's in between. Probably best to add another routine to go to the
MergeSheet and delete rows that have Canceled in column A after all of the
work performed by the loop in this code is finished. Here's my modification
to your displayed code
Sub Test3()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim shLast As Long
Dim sRow As Integer ' jlatham

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If Not IsEmpty(sh.Range("A4")) Then 'jlatham
Last = LastRow(DestSh)
shLast = LastRow(sh)

'This example copies everything, if you only want to copy
'values/formats look at the example below the first example
sh.Range(sh.Rows(sRow), sh.Rows(shLast)).Copy _
DestSh.Cells(Last + 1, "A")
sRow = 4 ' jlatham
End If ' jlatham
End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

"Dallman Ross" wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

In , Peo Sjoblom
spake thusly:

http://www.rondebruin.nl/summary.htm

Excellent stuff there. Thanks. I'm trying it out.

I find Ron de Bruin's stuff highly useful. I appreciated
Mr. Latham's code too. I am studying all of it.

I have to settle on something, and for now I am working through
Ron's code, but this one rather than the summary macro peo showed us:

http://www.rondebruin.nl/copy2.htm

I have stuff I don't care about on the first row, and
rows 2-3 are repeated headers. So I went with this part
from down lower on Ron's page I just cited:

Copy from row 2 till the last row with data

That is working. I've started on Row 4, where my data begins.
It worked well. But now I want to fix it up. Here are
my first 3 concerns:

1) I want to copy the header rows (2-3), but only from the first sheet
copied.

2) In Row 1 of the sheets is something potentially helpful to the macro:
it says:

Page 1 2 3 4 5

I have that row hidden on the sheets, but I would like the macro
to look there and find that "5" is the last sheet and not bother
trying to copy (empty, but for the header rows) sheets after that
in my workbook.

(Sometimes there are more or fewer pages to the data. I have
the queries set up for more than I expect to need, and the last
pages end up blank.)

Alternatively, we could not bother with that but just look to
make sure there is data on Row 4 (A4) and skip trying to copy
the sheet if there is not.


3) I don't want to copy rows that say "Canceled" in Column A.


Here's something else I added to the code already:

DestSh.UsedRange.Columns.WrapText = False 'dman
DestSh.UsedRange.Columns.AutoFit 'dman

I did that right after the loop through the workseehts closes. It
works as I'd hoped.

Thanks for any help here. Break it down -- if you can help
with any of (1), (2), or (3) I've listed, please post. I'd
very much appreciate it.

Dallman

P.S. Here is Ron's code that I'm using, except I'm copying from Row 4:

http://www.rondebruin.nl/copy2.htm excerpt:
---------------------------------------------
Copy from row 2 till the last row with data
[snip]

In addition to the information Peo has pointed out, I'll add this code as a
possible source of more information. As you asked, this is pretty primitive
and works in a rather confined scenario (row 1 has headers for all used
columns, and column A has entries for all used rows without empty cells until
last entry). This code adds it all to a new sheet each time it's run, not
into a new workbook.

Sub CombineWorksheets()
Dim newSheet As Worksheet
Dim anySheet As Worksheet
Dim rOffset As Long
Dim lastRow As Long
Dim lastCol As Long ' for Excel 2007
Dim lastColID As String
Dim maxRows As Long
Dim rangeToCopy As Range
Dim newLocation As String

'get last possible row number based on
'version of Excel in use
If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007
maxRows = Rows.Count
lastColID = "IV" 'last in pre-2007
Else
maxRows = Rows.countlarge
lastColID = "XFD" ' last in 2007
End If

'add new sheet to end of the book
Worksheets.Add _
after:=Worksheets(Worksheets.Count)
Set newSheet = ActiveSheet
'work through all sheets in the workbook
For Each anySheet In Worksheets
'don't process new sheet
If anySheet.Name < newSheet.Name Then
'find last row based on
'a column we can expect to
'always have data all the way
'down the used area on a sheet
'this column could be different
'for each sheet, but code assumes
'that column A is good for this on
'all sheets
lastRow = anySheet.Range("A" & maxRows).End(xlUp).Row
'this assumes that you have a header row
'in row 1 of the sheets that has no
'empty cells until the 'list' ends
lastCol = anySheet.Range(lastColID & "1").End(xlToLeft).Column
'set up to grab all used information
Set rangeToCopy = anySheet.Range("A1:" & _
Range("A1").Offset(lastRow - 1, lastCol - 1).Address)
'set up to put the values from rangeToCopy
'into on the new sheet in head-to-tail fashion
rangeToCopy.Copy
newLocation = Range("A1").Offset(rOffset, 0).Address
'paste the values into the new sheet
newSheet.Range(newLocation).PasteSpecial xlPasteValues
rOffset = rOffset + rangeToCopy.Rows.Count
End If ' sheet name test
Next ' anySheet loop
End Sub


"Dallman Ross" wrote:

I know I've seen tips on this here before, but I can't
find them now.

I have set up some web-queries that are on 6 sheets
in a workbook. (The source data is paginated; setting
up 6 queries, one for each page, was my solution.)

Now I want to work out a macro to combine the data from
the pages into a new workbook. I'd like to copy various
of the cells based on filtering criteria and edit others.

But we could start out more simply. Would someone be able
to get me going in the right direction with some sample
VBA that just cycles through the pages and finds and copies
the ranges of data into the new sheet? Would be most appreciated.

--
dman

In , JLatham
<HelpFrom @ jlathamsite.com.(removethis) spake thusly:

In addition to the information Peo has pointed out, I'll add this code as a
possible source of more information. As you asked, this is pretty primitive
and works in a rather confined scenario (row 1 has headers for all used
columns, and column A has entries for all used rows without empty cells until
last entry). This code adds it all to a new sheet each time it's run, not
into a new workbook.

Thanks very much! I'll definitely try it out. It may take me
a few days to make something of all this stuff, but I'll try to
report back.

Dallman

========================================
Sub CombineWorksheets()
Dim newSheet As Worksheet
Dim anySheet As Worksheet
Dim rOffset As Long
Dim lastRow As Long
Dim lastCol As Long ' for Excel 2007
Dim lastColID As String
Dim maxRows As Long
Dim rangeToCopy As Range
Dim newLocation As String

'get last possible row number based on
'version of Excel in use
If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007
maxRows = Rows.Count
lastColID = "IV" 'last in pre-2007
Else
maxRows = Rows.countlarge
lastColID = "XFD" ' last in 2007
End If

'add new sheet to end of the book
Worksheets.Add _
after:=Worksheets(Worksheets.Count)
Set newSheet = ActiveSheet
'work through all sheets in the workbook
For Each anySheet In Worksheets
'don't process new sheet
If anySheet.Name < newSheet.Name Then
'find last row based on
'a column we can expect to
'always have data all the way
'down the used area on a sheet
'this column could be different
'for each sheet, but code assumes
'that column A is good for this on
'all sheets
lastRow = anySheet.Range("A" & maxRows).End(xlUp).Row
'this assumes that you have a header row
'in row 1 of the sheets that has no
'empty cells until the 'list' ends
lastCol = anySheet.Range(lastColID & "1").End(xlToLeft).Column
'set up to grab all used information
Set rangeToCopy = anySheet.Range("A1:" & _
Range("A1").Offset(lastRow - 1, lastCol - 1).Address)
'set up to put the values from rangeToCopy
'into on the new sheet in head-to-tail fashion
rangeToCopy.Copy
newLocation = Range("A1").Offset(rOffset, 0).Address
'paste the values into the new sheet
newSheet.Range(newLocation).PasteSpecial xlPasteValues
rOffset = rOffset + rangeToCopy.Rows.Count
End If ' sheet name test
Next ' anySheet loop
End Sub


"Dallman Ross" wrote:

I know I've seen tips on this here before, but I can't
find them now.

I have set up some web-queries that are on 6 sheets
in a workbook. (The source data is paginated; setting
up 6 queries, one for each page, was my solution.)

Now I want to work out a macro to combine the data from
the pages into a new workbook. I'd like to copy various
of the cells based on filtering criteria and edit others.

But we could start out more simply. Would someone be able
to get me going in the right direction with some sample
VBA that just cycles through the pages and finds and copies
the ranges of data into the new sheet? Would be most appreciated.