Help Require in VBA

[imrandelhi]

Hi Team,

Need your help for compile the data through VB, now I am compile this data manually so manually its take lot of time so please helps.

Attached data is available location & City wise data so if I have to compile this data from AHD (sheet) to Kolkata (sheet) then I have to go one by one sheet & copy & past I have to do.

So i want to compile this data through vba.

Going to explain you which type of data I have to compile and exact what I want.
Go to AHD sheet - if I want Y17 data with Cat (Category) wise for the entire sheet from (AHD to Kolkata).
Then I have to go every sheet then copy past thing I have to do but I want to take this data through VBA.
Sheet1 I have compile 2 sheet data from AHD to Bglr-1 this sheet we take only Y17 data Cat(Catgory ) Wise.

So please help for this and please help for compile this data through VBA.

If you want I will come on Hangout for better explanation.

Thanks in Advance.
Regards
Imran
+91 9999194698(India)

[SamT]

The sheets you have:
Master is the list of stores
Total is the totals of all stores (Category Report)
Sheet1 is what you need ("Annual Report"?)
All other sheets are store reports
Store Sheets are (tab) named with short store code name


What you want.
For every category (Column("A")) on every Store sheet, copy the User-selected Year (Column("B")) Rows to Sheet1.

Style and Format questions:

What name do you want for Sheet1? The name can be a patterned name.
Example: Pattern = "Annual Report" & (year number) >> Name = Annual Report Y17
Another example: Pattern = "Report From " & (begin date) & " To " & (end date) >> Name= Report From Jan-2014 To Mar-2014

At this time, on sheet1, in Column("B",) you are using the short store code Tab name. If you want to use the complete store name from Sheet Master, Column("E",) you must put a cross index column (Maybe you can use Master column("D"),) with the Store code name in the same row as the store name. The cross reference column can be anywhere on the sheet, and, it can be hidden from viewing. You can also set the font color the same as the Fill (or Shade) color

[imrandelhi]

Thanks for replay:-

What i want : i want to compile file data from ahd sheet to kolkata sheet and if i select y17 or y18 then through vba all the data consolidate.

Style and format thing :- sheet1 column("b") leave i will add this using a formula.

so now please help for compile this data.

Thanks in advance

Sheet1 name is Consolidate data and

[imrandelhi]

Hey,waiting for your revert ("-")

[SamT]

Sheet1 name is Consolidate data andAnd what?

[SamT]

imrandelhi,

Writing a simple script that will work with this workbook is not too hard, but...

That code would need to be replaced or modified if the workbook ever changed for any reason.
Work with two "Consolidate Data" sheets in the book


Open a new store
Close, sell, or lose a store
Add another column to the Month sections (Qty, Val, Inv, + ???)
Management wants to track more years.
Management wants to track the preceding 12 months rather than fiscal year months.
Change the store code from an integer to anything else
Many more changes I can not imagine.


Writing a robust system of code that can handle these kinds of changes is beyond the scope of the VBA Express help forums. The people who "work" at the help Forums are alll unpaid volunteers who "work" here because they love teaching VBA code. Most of them do not have time available in their daily lives to contribute a project of this scope.

However, VBA Express does have a group of high ranking members who write this kind of code professionally. you can contact them at:
Link: Consulting Services (http://www.vbaexpress.com/consulting.php)
Link: Contact Us (http://www.vbaexpress.com/forum/sendmessage.php)
Telephone: (USA) 844-922-4357 (844-VBA-HELP) Toll free in USA and UK
Private message: PM (http://www.vbaexpress.com/forum/member.php?11-Zack-Barresse)



Finally, I cut down the Data book file size to show only what a programmer needs to know to write the code. See attachment.

[imrandelhi]

Hey Samt,

Not available any macro coding in provide file so please check and provide the updated file with macro coding.

Thanks

[jonh]

Back up your spreadsheet(s) before trying this, obviously.

Add a Sheet2 to your workbook.
On the master sheet write all the sheet names in column H, eg H3 = Bglr-2, etc.
Add the code below to a new module
run CopySheets

You are prompted for the year, e.g. type Y17
Any data already on Sheet2 is cleared and the new data copied to it.
Copy from sheet2 as required.

You will notice that the Cat(Category) column in the other sheets has changed.
If you run the code again *for the same year* no data will be copied unless you blank out the Cat(Category) cells again first.

Hope it helps

Sub CopySheets()
On Error GoTo err_hdlr
Dim yr As String, r As Integer: r = 2
Sheets("sheet2").Activate: Cells.Delete
Range("A:A").ColumnWidth = 35: Range("A1").Select
yr = LCase(Trim(InputBox("Enter Year"))): If yr = "" Then Exit Sub
With Sheets("master")
Do
i = .Cells(r, 1): s = .Cells(r, 8)
If i = "" Then Exit Do
If s = "" Then Exit Do Else _
If Not CopySheet(CStr(s), CInt(i), yr) Then Exit Do
r = r + 1: If r > 1000 Then Stop
Loop
End With
err_hdlr: Err.Clear
End Sub
Private Function CopySheet(s As String, i As Integer, yr As String) As Boolean
On Error GoTo err_hdlr
With Sheets(s)
.Range("A:A").UnMerge: r = 4
Dim lst As String
Do
If .Cells(r, 1) = "" Then
If .Cells(r, 2) = "" Then Exit Do
If LCase(.Cells(r, 2)) = yr Then
.Cells(r, 1) = i & " : " & s & " : " & t
If Len(lst) Then lst = lst & ","
lst = lst & r & ":" & r
End If
Else
t = .Cells(r, 1)
End If
r = r + 1: If r > 1000 Then Stop
Loop
If lst <> "" Then
.Range(lst).Copy: Sheets("sheet2").Paste
Range("A1").End(xlDown).Offset(1, 0).Select
End If
End With

err_hdlr: CopySheet = Err.Number = 0: Err.Clear
End Function

[imrandelhi]

Lots of Thanks to you - i am try to using provide coding and same its working but not getting all sheet data only getting AHD sheet data so please confirm whats we have to do.

Just to now if i have do any change in data such as YEAR adding and if add more sheet then this coding working ?

Please confirm,

Thanks
Imran(+91 9999194698)

[jonh]

You can add as many sheets/rows as you like as long as they are in the same format as the example you posted and you add the sheet names to Master.


I can't look into why it isn't working right now but the code will stop if there is an error, or if there is a break in data / missing values.
E.g. add all of the sheet names to 'master' and make sure they are spelled correctly.


Try changing the last few lines of code from



End With

err_hdlr: CopySheet = Err.Number = 0: Err.Clear
End Function


to



End With
CopySheet = true
exit function
err_hdlr:
msgbox err.description
Err.Clear
End Function


which should tell you if there is an error.

[jonh]

Can't edit my post.

I copied the code into a fresh version of the workbook you linked and it works for me without any problems, from ahd to hyd.

[imrandelhi]

Hey Jonh,

Thanks and its fine and provide coding working but getting one problem when we are running the macro then all the sheet from ahd to kolakata Category column data is goning to change so i dont want this to change so please check.

One more thing i want to from ahd to kolkata sheet one more data is available which name is youth combination,youth digital,youth analog and youth ladies so i also want this data to sheet2.

Thanks for your support Jonh Boss,I also want to learn VBA but i dont have time to go to take classes so please confirm any confirm any online website where we can to and learn and i you have any material for learn VBA so please confirm.

below is the snapshot for your reference and i want only category name -



Open Master Sheet



Cat(Category)







A



1 : Ahd : A











B



1 : Ahd : B











C



1 : Ahd : C











D



1 : Ahd : D











E



1 : Ahd : E











F



1 : Ahd : F











G



1 : Ahd : G











H



1 : Ahd : H











I



1 : Ahd : I











Total



1 : Ahd : Total

[imrandelhi]

Thanks for your support Jonh Boss,I also want to learn VBA but i dont have time to go to take classes so please confirm any online place where i can go and learn and take help and if you have any material for learn VBA so please provide.

[jonh]

No problem, glad it's working for you. :-)

As I said : "You will notice that the Cat(Category) column in the other sheets has changed."
It was an easy way of filling in the data to copy back to sheet2 and it's one of the reasons I told you to back up your workbook.

You could run the code in a copy of your workbook and then copy from there back into your master workbook.

The "Youth..." rows all have the same year so I don't know how you want that to work. As SamT said, I don't mind helping out if I can but I'm not here to write a whole system for you.

As for learning Excel VBA it's pretty simple.
Search the help file for things like 'if', 'do' and 'for' and read how to use those statements.
Nearly everything else you can work out by recording a macro and copying out the parts you need.
It will take time and practice but you'll get the hang of it.
Good luck.

[imrandelhi]

Hey Jonh,

For youth combination data which is available in below of total data and using same coding but not getting youth series data so please check & help.

I have more thing for ask on VBA so i will send you for updation.

Thanks

[imrandelhi]

Hey Jonh,

Waiting for your revert.

Thanks
Imran

[imrandelhi]

waiting for your revert.