Copy rows from several sheets into one sheet and add columns to indicate which worksheet they came from

[jamesbrown008]

I am trying to write a macro copy data from several worksheets into one.
The macro should only cycle through sheets with a "-" in the name
The data is very clean with no headers, totals or blank rows to worry about
What I need is for all rows from all sheets to be copied to one master sheet.
The above part is fairly standard stuff and I could likely work this bit out on my own.
However, here is the catch:
There are two types of sheet:
Agent Conversions and Online Conversions
The naming conventions are as follows
Agent Conversions d-m-yy (technically the date format can be either d-m-yy or dd-mm-yy)
Online Conversions d-m-yy (technically the date format can be either d-m-yy or dd-mm-yy)

What I need is for every row I copy from each worksheet to have the conversion type and the date broken out into two seperate fields on each row.
e.g. if I had a sheet called "Agent Conversions 4-7-11" and the first row had the following values:
Column A: "James" Column B: "47"
The macro would need to create a new row on the Summary tab with the following
Column A: "Agent Conversions" Column B: "4-7-11" (doesn't have to be date format - can be text) Column C: "James" Column D: "47"
It would then of course continue through all rows and all sheets.
If this is something any of you can do I will be forever in your debt.
Thanks
James

 

[Peter_SSs]

James

Try this in a copy of your workbook. The code assumes the 'Master' sheet already exists but clears any existing data from it before populating it with the current data.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ToMaster()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Master") <SPAN style="color:#007F00">'<-Change to suit</SPAN><br>        .UsedRange.ClearContents<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>            n = ws.Name<br>            <SPAN style="color:#00007F">If</SPAN> InStr(n, "-") > 0 <SPAN style="color:#00007F">Then</SPAN><br>                a = Left(n, InStrRev(n, " ") - 1)<br>                b = Right(n, Len - Len(a) - 1)<br>                ws.UsedRange.Copy Destination:=.Range _<br>                        ("A" & .Rows.Count).End(xlUp).Offset(1, 2)<br>                    rws = ws.UsedRange.Rows.Count<br>                .Range("A" & .Rows.Count).End(xlUp).Offset(1) _<br>                    .Resize(rws, 2).Value = Array(a, b)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jamesbrown008]

That is great Peter thanks, I will test it in the morning.

Thanks

James

 

[jamesbrown008]

Hi Peter - it comes up with: Invalid Procedure Call or Argument on this line

a = Left(n, InStrRev(n, " ") - 1)

 

[Peter_SSs]

Hi Peter - it comes up with: Invalid Procedure Call or Argument on this line

a = Left(n, InStrRev(n, " ") - 1)

Hmm, works fine for me.

What version of Excel are you using?

In the meantime try replacing that line with

a = Left(n, InStr(1, n, "Conversions") + 10)

 

[jamesbrown008]

Thanks Peter - I actually modified your original code from:

a = Left(n, InStrRev(n, " ") -1)

b = Right(n, Len - Len(a) -1)

to

a = Left(n, InStrRev(n, " "))

b = Right(n, Len - Len(a))

Thanks so much for all your help - I really appreciate it

 

[Peter_SSs]

Thanks Peter - I actually modified your original code from:

a = Left(n, InStrRev(n, " ") -1)

b = Right(n, Len - Len(a) -1)

to

a = Left(n, InStrRev(n, " "))

b = Right(n, Len - Len(a))

Thanks so much for all your help - I really appreciate it

I'm glad you seem to have got where you wanted to go but I can't see how that change would affect any error message you were getting on that "a = " line.