VBA question regarding copying data from several worksheets in one

[jamesbrown008]

Hi All

I am 100% new here so sorry to dive right in with a huge request.

I have been reading through a ton of forums and although I see a lot of code that is close to what I am looking for I haven't yet found one to that I can modify to fit my needs.

OK so here is the story




I administer an ecommerce system and produce two daily reports

• 1. Customer entered sales by Agent (Online Conversions)
  2. Agent enterd sales by agent (Agent Conversions)

The two reports are ftp'd to me and go into a folder for the current month e.g. April

I already have macro which I run daily to combines copy one worksheet from each of the daily reports into one master file. (I can share if anyone is interested)

This leaves me with a file that has numberous tabs for Online conversions and Agents conversions.

The macro I use to create the master file also appends a date to the end of each tab.

e.g.

Agent Conversions 4-1-11
Agent Conversions 4-2-11
Agent Conversions 4-3-11

Online Conversions 4-1-11
Online Conversions 4-2-11
Online Conversions 4-3-11

On every tab there is a list of agent names that varies in length from tab to tab.

The first thing I want to do is have a macro that accesses every tab and then strips some obselete rows from the bottom - I never know which line these rows will start on but I do know that I want to delete every row after and including the row where the word "Total" appears in the column
e.g. if the word Total appear in cell A22 - everything after A22 will be deleted)

The next macro I need (appologies for the complexity of this request) will be run each day to open each tab and copy evrything from from row 8 downwards into the summary tab. The code will ideally keep track of which tabs have been processed so that each tab is only processed once in a month or simply run for every tab and overwrite the summary tab from scratch each time it is run.

The only other catch it this: When the rows are copied over I need to populate a field in each row with the the name of the tab that it came from.

If I am dreaming here and this is too large a request to burden you fine people with I fully understand but thanks for reading anyway.

James

 

[B.brazilian]

I dont have the answer for you question, but I have a very similar problem that I am trying to figure a way out of it.

I work for a company where we have to sen form to different branches and they fill out those excel forms and email back to us.
Then I have to go thru every form and copy the data and paste into an another workbook here at the corp office.

The fields are the same the format is basically the same, therefore it is a very boring and time consuming task of copying and pasting from one workbook to another.

So i was wondering if it would be possible to create a macro using VBA coding to get the data from the workbooks that we get back on the emails to the main one here at the office?

Thanks in advance.
BR

 

[jamesbrown008]

If it helps - I can send you my macro for combining the daily workbooks into a monthly workbook.

It makes it more useable but the data is still on seperate tabs (there in lies my problem)

 

[B.brazilian]

I'd appreciate if you could do that. I am kind of lost with this so honestly i dont even know how and/or where to start haha

any help is welcome!!!

 

[mcmatlarge]

Please if you will send that my was I'd really appreciate it, thats exactly the type of code I was just looking for

 

[jamesbrown008]

Please if you will send that my was I'd really appreciate it, thats exactly the type of code I was just looking for

If you guys email me I will send it to you - I can't work out how to upload it

removed

 

[Peter_SSs]

James

Note that I have removed your email address. Refer to point 19 of the Posting Guidelines and also #7 in the Forum Rules

 

[jamesbrown008]

Totally understand Peter - thanks

 

[Peter_SSs]

Totally understand Peter - thanks

Cheers - should save your inbox from some spam at least.

 

[EB08]

Not sure where this one stands right now, but if I understood the OP correctly something like this should get you started.

Public Sub Test()
Dim lngRow As Long
Dim lngCount As Long
Dim strSheet As String, strID As String
Dim wsSheet As Worksheet
Dim rngCell As Range

strID = " X"    'Using " X" as a modification to sheet name to ID sheets that have already been processed

For Each wsSheet In ThisWorkbook.Worksheets
    strSheet = wsSheet.Name
    lngRow = Sheets("Master").Range("A65536").End(xlUp).Row + 1 'Named consolidating sheet "Master"
    If Not InStr(strSheet, " X") > 0 And Not InStr(strSheet, "Master") > 0 Then 'If not Master and not already marked with " X"
        lngCount = Application.WorksheetFunction.Match("Total", wsSheet.Range("A1:A" & wsSheet.Range("A65536").End(xlUp).Row), 0)   'ID the total row so that we don't bring data beyond it
        For Each rngCell In wsSheet.Range("A8:A" & lngCount)
            Sheets("Master").Range("A" & lngRow & ":B" & lngRow).Value = rngCell.Resize(1, 2).Value 'These ranges will adjust for you, but just to give you an idea where I was going with this
            Sheets("Master").Range("C" & lngRow).Value = wsSheet.Name   'Here is a way to ID the sheet you pulled the data from
            lngRow = lngRow + 1
        Next rngCell
        wsSheet.Name = strSheet & strID 'Updating the sheet name with the " X" string so that the next time you run the procedure, it skips over the processed sheet
    End If
Next wsSheet
            
End Sub