Solved: Merging Sheets of Data

[danesrood]

Dear All

I know that this has been asked for many times over the web in various guises but I hope that someone could help me with something a bit more flexible that may be of help to others.

I would like to be able to copy the data on selected identically structured sheets chosen at runtime from one workbook to a reporting page.

The heading on row 1 should only be copied over once and as there are helper columns A-C containing formulas for hundreds of possibly empty rows on each sheet I would like to be able to select the column which does not have any formulas that would be the same on all sheets (at present D but this could change) that is to be used to calculate how many actual rows of data has to be copied over.

I hope that this all makes sense and as always my sincere thanks for any help that you can provide

Many thanks to anyone who help.

Danesrood

[aravindhan_3]

Hi,,

confusing.. can u please post a sample workbook?

cheers
Arvind

[danesrood]

Hi

Sorry for the delay in geting back to you.

I have attached an example sheet where for confidentiality reasons I have had to juggle some of the data but the structure is correct. Basically for various reasons there are formulas at present in columns A-C down a few hundred rows on each of 9 sheets but I only want to copy over the rows that currently extend from D2 to whatever.

I hope that it makes sense.

I feel a bit embarrased in that a similar thread has appeared that for some reason I missed.

Anyway your interest is most appreciated

[mdmackillop]

Try this

[danesrood]

mdmackillop

Thank you so much for your reponse which is really good and does what I wanted.

As always there is a desire for a tweak or two.

In trying to make it as flexible as possible could you provide an option on the form to completely empty the summary sheet of data from rows 2 onwards.

And finally, as there is a good chance that I will need to change from time to time the column that is used to quantify the number of rows to copy (currently D) could you please identify what I would need to change.

Again my sincere thanks for the time that you have taken.

Regards

Danesrood

[mdmackillop]

amended

[danesrood]

mdmackillop

Thats wonderful other than one issue that I only picked up through an oversight on my part. I added another two sheets but forgot to put in any data so that they just had the heading in row 1. When I ran the macro it pulled in the header row twice rather than ignoring it as it should.

But I have to say that this is better than I could have hoped for because with the flexibility of choosing the sheets and columns I can see that I will be using this regularly.

Danesrood

[WINFS]

I'm testing the file but it seems that the row copied always the same regardless which column was chosen in the form. Am I missed out something?

regards,

[mdmackillop]

[VBA]If sh.Cells(Rows.Count, Col).End(xlUp).Row > 1 Then
Range(sh.Cells(2, Col), sh.Cells(Rows.Count, Col).End(xlUp)).Offset(, 1 - Cells(1, Col).Column).Resize(, 17).Copy _
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If[/VBA]
WINFS,
Possibly not all columns will contain data. If the date was missing, you could use Column E

[danesrood]

mdmackillop

That is it. Wonderful.

Thank so much this will save me hours and hours of copying etc. Maybe it will be of help to others with the built in flexibility.

WINFS
Just to explain my problem a bit more, for various departmental reasons I have to have columns A-C and possibly more full of formulae and it is only the first actual data column on each sheet that determines how many actual lines of data ned to be copied over.
If people don't have my problem then they can leave it set to column A

Again my sincere and grateful thanks for the time that you have spent on this, it is much apreciated.

Danesrood


PS
Oops I've just thought of one tiny extra that would help but if I'm pushing my luck don't worry, what you have already done is great. As I have 9 or so sheets could the selection boxes default to having them all checked and I could just uncheck the odd one that is not required. I promise that this will be the final request.

[mdmackillop]

No problem,
just add the line
[VBA]
.Controls("Checkbox" & j).Value = True
[/VBA]

[WINFS]

Hi Danesrood

can you confirm if you have the results you wanted? Because on my pc,regardless which Column entered in the form, it copies the entire row starting from column A of the data sheets into the Summary sheet. But, I only expect it copies from column D.

[mdmackillop]

Hi Winfs
Enter data in Column D in a sheet and run this code. It will demonstrate the range that is copied.
Regards
MD

[VBA]Sub SelectFromD()
Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)).Offset(, -3).Resize(, 17).Select
End Sub
[/VBA]

[danesrood]

mdmackillop

Thank you that's fine. Again my thanks for all of your time in sorting this out for me.

Winfs
Yes the code is working exactly as I want. It is using the selected column - D at present to work out how many rows of actual data need to be copied. At present on all of the data sheets I have pretty basic formulae in rows 2-500 but at any given time any number of rows are filled with data and very rarely no data at all so on those occassions no data is to be copied. Oh yes and it is the complete row that has to be copied not just from the column that is used to calculate the number of rows. I hope that makes sense.

Again mdmackillop my thanks.

Regards


Danesrood

[danesrood]

mdmackillop

I have now used this code with real data and a minor problem has arisen. If for some daft reason you press the Clear button twice or when the summary page is already empty then it wipes out the header row.

Not the end of the world but it would be helpful if it didn't happen.

Danesrood

[mdmackillop]

We all dio these daft things though, so
[VBA]
Private Sub CommandButton2_Click()
Range(Cells(2, 1), Cells(5000, 1)).Resize(, 17).ClearContents
End Sub

[/VBA]

[danesrood]

mdmackillop

I am sorry to reopen this thread as the code you previously wrote for me is wonderful and does everything that I want.

But and there always seems to be one, I have some problems with colleagues using this which requires me to ask if it could be adjusted slightly into a single macro whereby the tab names to be merged are actually hard coded as is the column that is used to determine the number of rows to merge. The summary sheet should be cleared out before it runs.

Again my sincere apologies for going over old ground.

Grateful thanks.

Regards

Danesrood

[mdmackillop]

Hi
Can you post a file containing the current code?

[danesrood]

mdmackillop

Sorry for not getting back to you last night but I did not have the code available.

Sheet attached where I have changed sensitive info but the actual named sheets other than the last three are the ones currently in use.

What I want to do as limiting as it might seem is to hard code the office names along with the column letter which is currently F.

I would like to reiterate that for me your existing code is great.

Many thanks

Danesrood

[mdmackillop]

[vba]
Sub HardCoded()

Dim Arr(), a
Dim rng As Range
Dim sh As Worksheet
Dim Col As String

ReDim Arr(20)

Arr(0) = "Canterbury"
Rem Arr(1) = "Cosham" 'Comment out if not required
Arr(2) = "Hastings"
Arr(3) = "Ramsgate"
'and so on

Col = "H" '<=== Change to suit

Sheets("Summary").Range("A2:S10000").ClearContents

For Each a In Arr
If a <> "" Then
Set sh = Sheets(a)
If sh.Cells(Rows.Count, Col).End(xlUp).Row > 1 Then
Range(sh.Cells(2, Col), sh.Cells(Rows.Count, Col).End(xlUp)).Offset(, 1 - Cells(1, Col).Column).Resize(, 17).Copy _
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
End If
Next

End Sub

[/vba]