• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro for consolidate the data.

R

Ramesh Deo

Member
Hi Members!!

Good morning All,

There is a question on creating macro to consolidate the data, so i need help in this.
I tried to it through recording macro but its not working properly.

I have two workbook(Excel file), one is data source workbook(Excel file) named Tracker Sheet- CRM-6-20 with multiple sheet tabs e.g Report, Week 1, week 2, week 3, Week 4(20-25 Jan), Week 5(27-01 Feb),Week 6(03-08 Feb) like wise. I have copy the data form Week 18(28-03 May) to till week 25 in second one workbook(excel file) named Updated Traffic-score Sheet-6-20 in data sheet-tab.

kindly help me out.

Thanks in advance!!
 
Hi Ramesh

What exactly are you trying to do? You have described your worksheets from 1 to 6 and 18 to 25? What about the ones in between and what do you want to do with the worksheets. You need to give a bit more clarity to get a reasonable answer.

Take care

Smallman
 
Smallman said:
Hi Ramesh

What exactly are you trying to do? You have described your worksheets from 1 to 6 and 18 to 25? What about the ones in between and what do you want to do with the worksheets. You need to give a bit more clarity to get a reasonable answer.

Take care

Smallman
Click to expand...

"worksheets from 1 to 6 and 18 to 25?" its a different tab(sheet) in a workbook or excel file named "Tracker Sheet- CRM-6-20" and i want to copy the data from sheet named Week 18(28-03 May) to till week 25 (Whatever the data in the sheet between given sheet name) and paste into second one workbook(excel file) named Updated Traffic-score Sheet-6-20 in data sheet or tab.

I think you got it !!!
 
Ramesh

I still don't understand. You will help your cause if you post a workbook with a before and after view of your problem, showing the sheet tabs you want consolidated before and a tab with the consolidated data after the procedure has run.

Take care

Smallman
 
Smallman said:
Ramesh

I still don't understand. You will help your cause if you post a workbook with a before and after view of your problem, showing the sheet tabs you want consolidated before and a tab with the consolidated data after the procedure has run.

Take care

Smallman[/quote

Hi smallman,

Please find the uploaded file for the query, I have uploaded both file. u can see this. and do the needful.
Click to expand...
 
please find attached file.
 

Attachments

  • Tracker Sheet- CRM-6-24.xlsx
    46.9 KB · Views: 7
  • Updated Traffic-score Sheet-6-24.xlsx
    20.5 KB · Views: 2
Hi Ramesh

Locically when you are providing a sheet which has the after the procedure is complete view you should have data on that sheet. For example you might want to include the finished version the procedure with all three weekly tabs from the Tracker Update … xls file. This logically gives someone like me a fair chance at seeing what you are trying to do. Leaving the Data sheet blanks is a lottery and I have to make guesses at what you are trying to achieve.

Using guess work this is what I think you want to do.

Code: 
Option Explicit
Sub Update()
Dim ws As Worksheet
Dim sh As Worksheet
Dim ow As Workbook
Set sh = Sheet3
Set ow = Workbooks.Open("D:\Tracker Sheet- CRM-6-24(1).xlsx")
  For Each ws In Sheets
  If Left(ws.Name, 4) = "Week" Then
  ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Resize(, 28).Copy _
 sh.Range("A" & Rows.Count).End(xlUp)(2)
  End If
  Next ws
  ow.Close False
End Sub


The hiding of the ReOpen Task sheet caused me to scratch my head for a bit too. Might be an idea to get rid of that sheet all together. I would not include it.

You will need to change the procedure so your File path is aligned to where the Tracker sheet is saved.

I have tested this and it consolidates all sheets from the Tracker Sheet into the Data tab or the Updated file.

Take care

Smallman
 
i tried this macro but its not working smallman. see, i have uploaded the tracker sheet as a sample with only three tabs because i got error while uploading ,its a large file, but what i have tracker sheet with multiple tab(like week 1 to week 26). you can add the tab with same data
Now i want to copy the data only from week 18(28-03 July) to till week in updated traffic score file into data tab simply.
note:- Both file saved on desktop
now please get me a clear concept if possible.
 
Hi Ramesh

The above is a very clear concept. I have been using it for a lot of years and as I said in my first post I already tested it on your data and it flew through - worked perfectly. You need to check a few things - firstly have you got the path right? Secondly, do you have the name of the XL file exactly correct? Thirdly did you provide a like for like file on this site making sure there were no Merged cells?

These are all important.

How about you test it by putting the file on your C drive to keep things simple then just change the D drive in the file path with C drive.

This should get you pointed in the right direction.

Take care

Smallman
 
No it won't do that. As I said from the start I was using the force to try and work out what you wanted as I could not understand any of your requests.

I suggest you have a list of the weeks you want to include in a tab originally titled "List". The list will comprise the sheet names to be upload ie

Week 18...
Week 21
Week 25
etc

Then you should iterate (Loop) through this list and upload each of the worksheets matching that sheet name.

Hope this helps.

Smallman
 
As I am beginner about macro, so can please suggest me, where should be change in loop as you said for what i want result..
please help me out!!
thanks smallman
 
Ramesh

You are walking into the world of programming so I suggest you get clued up in the basics of vb if you want to be a bit more self reliant. The code I will provide iwill require you to change the file path and ensure you maintain the data table on the tab I have called LIST. Just put the sheets you want uploaded in the Yellow Area but make sure the sheet names are exact.

This is all you really need. Once again I have tested this procedure and it runs nicely.

Code: 
Option Explicit

Sub Update1()
Dim sh As Worksheet
Dim ow As Workbook
Dim ar As Variant
Dim i As Integer

Set sh = Sheet3 'This is the Data sheet (sheet 3 is the Code name)
'ar stores the List
ar = Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
Set ow = Workbooks.Open("C:\Tracker Sheet- CRM-6-24.xlsx")

'The following is the Loop - it will go from item 1 in List to the Last item you want uploaded.
  For i = 1 To UBound(ar)
    Sheets(ar(i, 1)).Range("A2", Sheets(ar(i, 1)).Range("A" & Rows.Count).End(xlUp)).Resize(, 28).Copy _
    sh.Range("A" & Rows.Count).End(xlUp)(2)
  Next i
  ow.Close False
End Sub

I have uploaded a file to show how it is meant to work. Just replace that file path.

Take care

Smallman
 

Attachments

  • Updated Traffic-score Sheet-6-24.xlsm
    31.3 KB · Views: 7
You must log in or register to reply here.
Back
Top