combining worksheets 1

[spamjim]

I am way out of my element in Excel VBA. I would have organized this in a database but it is not my project.

I need to combine several worksheets from the same workbook into one worksheet. I don't need the first 2 and the last 6 worksheets so I am selecting worksheets by specific names in the macro below. I also don't expect more than 500 rows per worksheet so I'm copying B9:AL509 and ignoring blanks when pasting. This is what little I know so any suggestions for improvement are welcomed.

What I don't know is how to only copy or paste into the merged worksheet if there is a value in the first column (B) of each row. There may be content in other rows but I don't want the row if there is no data in B column.

I also have no clue how to append one row after another as the macro jumps from worksheet source to worksheet source. In the macro below, I'm just advancing 10 rows for each worksheet paste as a logic placholder, whether the original worksheet had 2 rows or 50 rows.

Can anyone clue me in to how to select only rows with a value in B column and how to paste these in order on the merged worksheet?




The macro recorder helped me get to this mess below...

Sheets("My first worksheet").Select
Range("B9:AL509").Select
Selection.Copy
Sheets("MERGED").Select
'select new location
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    
    
Sheets("My second worksheet").Select
Range("B9:AL509").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MERGED").Select
'select new location
Range("A14").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
        
    
Sheets("My third worksheet").Select
Range("B9:AL509").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("MERGED").Select
'select new location
Range("A24").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

'...

 

[SkipVought]

Hi,

You might try this...

Dim ws as worksheet, lRow as long

For each ws in worksheets
  Select ws.name
    Case "first sheet name","second sheet name". 'Etc
        With ws.usedrange
           Range(.cells(2,1), .cells(.rows.count,.columns.count)).copy
        End ws
        With Sheets("MERGED")
            lRow = .[a1].currentregion.rows.count + 1
            .cells(lRow,"A").pastespecial xlpasteall
        End with
  End select
Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

Thanks but I'm seeing a syntax error on "Select ws.name

 

[SkipVought]

Sorry, my mistake

Dim ws as worksheet, lRow as long

For each ws in worksheets
  Select [highlight]Case[/highlight] ws.name
    Case "first sheet name","second sheet name". 'Etc
        With ws.usedrange
           Range(.cells(2,1), .cells(.rows.count,.columns.count)).copy
        End ws
        With Sheets("MERGED")
            lRow = .[a1].currentregion.rows.count + 1
            .cells(lRow,"A").pastespecial xlpasteall
        End with
  End select
Next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

Thank you! I'm getting very close. I tweaked cell ranges to accommodate 8 rows of headers in the source and 3 rows of headers in the MERGED target worksheet. I'm also throwing out column 1 from the source.

So I am better understanding the concept of appending rows to the target sheet but I still have a problem.

I need to ignore source rows with nothing in the first or second column. There are empty formulas in the other columns that end up getting copied in the range. So in one worksheet I might have 4 complete rows of data followed by 50 unneeded rows that happen to have incomplete formulas in one of the later columns. If only there was some way to conditionally select rows based on the presence of a value in column B...

Sub mergeworksheets()

For Each ws In Worksheets
    Select Case LCase(ws.Name)
    Case "sample", "s", "e", "merged"
         'Do nothing - we don't want these
    Case Else
        With ws.UsedRange
           Range(.Cells(9, 2), .Cells(.Rows.Count, 39)).Copy
            With Sheets("MERGED")
            lRow = .[a3].CurrentRegion.Rows.Count + 1
            .Cells(lRow, "A").PasteSpecial xlPasteValuesAndNumberFormats
        
            End With
        End With
    End Select
Next ws

End Sub

 

[SkipVought]

        With ws.UsedRange
           Range(.Cells(9, 2), .Cells(.Rows.Count, [highlight]39[/highlight])).Copy

So [highlight]THIS[/highlight] is not the last column in your table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

I only need 38 or 39 columns of a much larger table. There is some other junk starting around column 42 that I don't need.

Right now I am researching 'if/then' on particular cell values to copy one row at a time instead of a used range of rows. But I am always willing to be told a better way.

 

[SkipVought]

No, that's fine if that's your requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

I'm still struggling with this. I don't really need UsedRange because this value is not quite accurate. I only want to copy the first 39 columns of rows that have data in the first few columns (A, B, C). But there may be junk adding to UsedRange (empty formulas or formatted cells) in columns D & E that do not constitute a full row of data to copy. Is there a trick to getting the first 3 rows in this example? The full rows always appear at the top of the table.

[tt]
|A|B|C|D|E|F|...
|1|2|3|4|5|6|...
|1|2|3|4|5|6|...
|1|2|3|4|5|6|...
| | | |4|5| |...
| | | | |5| |...
[/tt]

I'm wondering if I should be copying/pasting row by row instead of a range of rows in red below.

Sub mergeworksheets()

For Each ws In Worksheets
    Select Case LCase(ws.Name)
    Case "sample", "s", "e", "merged"
         'Do nothing - we don't want these
    Case Else
[COLOR=#A40000]        With ws.UsedRange
           Range(.Cells(9, 2), .Cells(.Rows.Count, 39)).Copy
            With Sheets("MERGED")
            lRow = .[a3].CurrentRegion.Rows.Count + 1
            .Cells(lRow, "A").PasteSpecial xlPasteValuesAndNumberFormats
        
            End With
        End With[/color]
    End Select
Next ws

End Sub

 

[SkipVought]

The UsedRange property of a worksheet return th range that include the range from the TOP LEFT cell containing data (ANY data including formulas, maybe only an invisible SPACE character, cell formatting data, data of ANY KIND!) to the BOTTOM RIGHT cell containing data.

This may not necessarily be CONTIGUOUS data.

Since I deal with sheets/tables, one table per sheet most often, and I am certain that my tables are contiguous (no empty rows or columns within a table), I most often use the CurrentRegion range property.

Bottom line, you need to know 1) what is is that you need to accomplish and 2) what tools you have that can possibly be used to meet that need and 3) the means to evaluate the means to ascertain the best choice for a given scenario.

So you your example, "there may be junk..." in the columns to the right. How did that happen? Is thee a way to identify that junk logically? If so, it could be eliminated programatically.

Then you say, " The full rows always appear at the top of the table." ALWAYS in the first 3 rows? Or is there some other criteria for deciding "full rows?"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

The junk is simply formatted cells and formulas that have been copied down the column. The person that created the worksheet has prepared the file to accept more data in the future but they've only filled in the first few rows of data. They may have copied a formula down 500 rows in a column further to the right. But we're only needing to copy the first dozen rows with actual data in column A. The data to copy will be contiguous from the A1 origin. The range should stop when there is no data in the next row of column A.

My original vba to "SkipBlanks:=True" failed because of that junk in the later columns.

My logic should (hopefully) be as simple as checking column A, seeing if it is not "" and not EMPTY, and then copying the row or range (x,1)x,39) to the merged worksheet. Then looping to the next source row until we hit "" or EMPTY, as a trigger to go to the next source worksheet. I'm just getting lost on the syntax.

I'm certain that it would be more ideal to obtain a range of rows that have values in column A instead of copying/pasting each row but I suspect that this project may evolve in the future to also ask for copying rows only where column D = 1. I'd lose the ability to select contiguous data in a range at that point.

[tt]
|A|B|C|D|E|F|...
|1|2|3|1|5|6|...
|1|2|3|4|5|6|...
|1|2|3|1|5|6|...
| | | |4|5|#VALUE!|...
| | | | |5|#VALUE!|...
[/tt]

 

[SkipVought]

and formulas that have been copied down the column

Well there's part of your problem. Why would you do that? You enter meaningless unused data in rows, unnecessarily, causing all sorts of unexpected problems, as you no doube have been experiencing.

If you were to make your table a Structured Table, assuming that you have Excel 2007+, you would not need to copy formulas into unused rows! Get rid of them!

So what's the reason for the mess in columns to the right?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

So what's the reason for the mess in columns to the right?

Sadly, I am working with people more clueless than I.

This whole project is killing me. Instead of creating a single table database like this:

[tt]company|salesperson
XYZ|Jane Doe
ABC|Ward Kimball
XYZ|Sam Clemente
XYZ|Paul Revere
POP|Sven Gali[/tt]

They have created a workbook with a distinct worksheet for each company.

sheet1
[tt]company|salesperson
XYZ|Jane Doe
XYZ|Sam Clemente
XYZ|Paul Revere[/tt]

sheet2
[tt]company|salesperson
ABC|Ward Kimball[/tt]

sheet3
[tt]company|salesperson
POP|Sven Gali[/tt]

And now they want me to merge this mess into what it might look like if it was set up in a proper DB, as a single table. It is backward indeed. And their overall use of Excel is scary.

I've already tried linking Excel tables in Access but the quality of the data is so poor that even Access is useless. That is why I am back at Excel, trying to simply merge the worksheets.

 

[SkipVought]

The data must be clean.

The workbook maintenance procedures must be sound. If you users mess things up, you or someone will need to be constantly cleaning the data.

Why not LOAD the cleaned data into Access and design a form to Add, Change, Delete?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[spamjim]

A linked Excel table, loaded into Access, comes in as read only and with some faulty field formatting. It could be cleaned by transposing the data to a query "table" and then working from there. But the path of least resistance is becoming more apparent. I just need to update my resume and look for a smarter crowd to work with.

 

[SkipVought]

Well I've been in a similar circumstance, knowing what should be done, but not having the clout or the voice to get something constructive accomplished. That can be very frustrating.

You will have to make the decision to either strike out in a different direction, or remain in your current situation and endure the madness.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]