Hi Dinesh sir, thank you for such knowledge providing videos..it helps us a lot..... I have quiry if you can help in that.... I have multiple client name wise subfolder in a main folder 2022.and every subfolder has one QC tracker sheet with it client name.... I have to enter the cases on daily basis in these trackers. But not in all trackers..for example...if we received QC data for our 10 clients in a day then we will go those specific clients subfolder and sheet and then enter the the data.... could you please make a video on this topic...how to open specific sheets in through Loop in subfolder and enter the data....it will be a great help for me.... thank you in advance.!!
If all excel sheets in subfolder have the same format and the required data is scattered in different cells. Then how to put such data in a row in sheet2 (master sheet)?
Sir thank you for your videos. I have a folder and folder has sub folders and each sub folders have some number of file. All these file are linked to 1 source file which will change every month. Can you guide how using macro we can change link of all these files in subfolder to the new source file Your guidance will be of great help!!! Thank you!
@@Exceltrainingvideos so I have a source excel file which has forecast data. We have customer wise Excel sheets say 100 customers all these files are linked to source excel data. Now these customer files are split into separate country wise folder ex. L&t, HDFC Bank in India folder. Google Facebook in USA folder and these country wise folders is under respective Global leaders folders so structure is Folder Named Global Leader Under that Folder India, US Europe Under each of these folders there is customer wise Excel file which is linked to source excel file (forecast data) As month wise forecast data will change so each month I have to go to each folder open each file and then change link manually. So wanted to know how to automate this process of changing excel source file every month using Macro as all the 100 customer wise file is linked to single source fil
@@kiranmenon93 These VBA tutorials in Excel will guide: www.exceltrainingvideos.com/how-to-loop-through-subfolders/ www.exceltrainingvideos.com/tag/edit-multiple-excel-files-with-vba/
This link will guide: www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/ For more solutions search www.exceltrainingvideos.com
Sir I have embedded your vba code "to copy from non-adjacent cells" into the code you have taught in this video. It collects the data successfully and pastes all in one row. Here I need your help again. I wanted to paste data from each sheet in a separate row. (There is only one excel sheet in each sub-folder).
hello sir, could you please help me out in this code?)(i want to call a function (whose return value should be an array) in sub, also there is one argument as integer in that function, which will go from sub to that function. see this code. sub ram() k = 5 Call test(k) ' how to print this array "test", AND how to copy this" test array in other array??? End Sub Function test(k) As Variant Dim arr(0 To 100) As Variant For i = 0 To k arr(i) = i * i Next i test = arr() ' i want to return this arr to test End Function '##cant print test array and 13 mismatch error in immediate window i could not find your mail id. could you please give your mail id , so that i can send my problem in detail.
Sir in this code I have embedded your another vba code for copying data (in a row) from non-adjacent cells into one sheet. My aim is to create a summary of salary details (allowances, deductions, net pay, gross pay etc.) for all employees. For this purpose, I need to enhance the functionality of this code to enter details of each employee in a separate row. Currently it adds data from all excel sheets into a single row. Please guide how to resolve this issue. (1): When pasteRange is used outside this For loop, it creates runtime error 1004. For Each cel In copyRange cel.Copy ecolumn = Sheet1.Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Column pasteRange.Cells(4, ecolumn).PasteSpecial xlPasteValues Next And when used inside the loop, it pastes data as mentioned above. (2): I have placed the above For Loop inside Do-While loop in between two lines: set wbk.............................. wbk.close.......................... I have noted that row in pasteRange can be changed by changing the number 4 in the above lines. But getting no idea to go to the next line after the data for one employee has been copied. Please guide as early as possible. Thanks.
@@Exceltrainingvideos Sir it enters data in one column as: Name: Basic Pay: House Rent: Conveyance Allowance: Medical Allowance Name: Basic Pay: House Rent: Conveyance Allowance: Medical Allowance
I applied another approach. Collected all data in a row as: Name: Basic Pay: House Rent: Conveyance Allowance: Medical Allowance Name: Basic Pay: House Rent: Conveyance Allowance: Medical Allowance and then created another macro that follows as: For i = 6 To 32 Sheets("sheet1").Activate Range("B2:V2").Copy 'copy data about one employee Sheets("sheet2").Activate Sheets("sheet2").Range(Cells(i, "B"), Cells(i, "V")).Select 'This is the paste range ActiveSheet.Paste Sheets("sheet1").Activate Range("B2: V2").EntireColumn.Delete 'After pasting, it deletes the copied cells from sheet1. Next Here I selected paste range as you have taught in your lecture about (Transfer Specific Worksheet Range Data from one worksheet to another based on condition). Thank you so much for uploading lecture videos over such a diverse range of topics.
I was stuck and you got me over the hurdle, thank you so much!!!!
Hi Dinesh sir, thank you for such knowledge providing videos..it helps us a lot..... I have quiry if you can help in that.... I have multiple client name wise subfolder in a main folder 2022.and every subfolder has one QC tracker sheet with it client name.... I have to enter the cases on daily basis in these trackers. But not in all trackers..for example...if we received QC data for our 10 clients in a day then we will go those specific clients subfolder and sheet and then enter the the data.... could you please make a video on this topic...how to open specific sheets in through Loop in subfolder and enter the data....it will be a great help for me.... thank you in advance.!!
This link will guide: www.exceltrainingvideos.com/two-methods-to-loop-through-worksheets/
Ok... thank you Sir..!!
If all excel sheets in subfolder have the same format and the required data is scattered in different cells. Then how to put such data in a row in sheet2 (master sheet)?
Thanks Dinesh.
Awesome, thank you so much sir,
is there any video so i can learn from starting as I am a beginner.
You can start here: www.exceltrainingvideos.com/excel-vba/excel-2003-vba/
Sir thank you for your videos. I have a folder and folder has sub folders and each sub folders have some number of file. All these file are linked to 1 source file which will change every month. Can you guide how using macro we can change link of all these files in subfolder to the new source file
Your guidance will be of great help!!! Thank you!
Query is not clear. Please take some time to rephrase your query in detail.
@@Exceltrainingvideos so I have a source excel file which has forecast data. We have customer wise Excel sheets say 100 customers all these files are linked to source excel data. Now these customer files are split into separate country wise folder ex. L&t, HDFC Bank in India folder. Google Facebook in USA folder and these country wise folders is under respective Global leaders folders so structure is
Folder Named Global Leader
Under that Folder India, US Europe
Under each of these folders there is customer wise Excel file which is linked to source excel file (forecast data)
As month wise forecast data will change so each month I have to go to each folder open each file and then change link manually. So wanted to know how to automate this process of changing excel source file every month using Macro as all the 100 customer wise file is linked to single source fil
@@kiranmenon93 These VBA tutorials in Excel will guide: www.exceltrainingvideos.com/how-to-loop-through-subfolders/
www.exceltrainingvideos.com/tag/edit-multiple-excel-files-with-vba/
Hi Dinesh Sir-If there are multiple sheets and i want copy from specific sheet only let say sheet1?
This link will guide:
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
For more solutions search www.exceltrainingvideos.com
Thanks!
Thank you!
Very helpful
Sir if I want to copy specific cells let say C4,D16 and B12 from subfolders and paste it in the master sheets as u did...can u help me
This link will help: www.exceltrainingvideos.com/tag/copy-paste-non-adjacent-cells-excel/
Also search www.exceltrainingvideos.com
@@Exceltrainingvideos Yes this I am watching for. Thank you so much for this help. This is the exact solution to my problem.
Sir I have embedded your vba code "to copy from non-adjacent cells" into the code you have taught in this video. It collects the data successfully and pastes all in one row. Here I need your help again. I wanted to paste data from each sheet in a separate row. (There is only one excel sheet in each sub-folder).
hello sir,
could you please help me out in this code?)(i want to call a function (whose return value should be an array) in sub, also there is one argument as integer in that function, which will go from sub to that function.
see this code.
sub ram()
k = 5
Call test(k)
' how to print this array "test", AND how to copy this" test array in other array???
End Sub
Function test(k) As Variant
Dim arr(0 To 100) As Variant
For i = 0 To k
arr(i) = i * i
Next i
test = arr() ' i want to return this arr to test
End Function
'##cant print test array and 13 mismatch error in immediate window
i could not find your mail id.
could you please give your mail id , so that i can send my problem in detail.
is this possible in python ? if yes can anyone help me out ??
Looping is key in all programming languages!
Sir in this code I have embedded your another vba code for copying data (in a row) from non-adjacent cells into one sheet. My aim is to create a summary of salary details (allowances, deductions, net pay, gross pay etc.) for all employees. For this purpose, I need to enhance the functionality of this code to enter details of each employee in a separate row.
Currently it adds data from all excel sheets into a single row. Please guide how to resolve this issue.
(1): When pasteRange is used outside this For loop, it creates runtime error 1004.
For Each cel In copyRange
cel.Copy
ecolumn = Sheet1.Cells(4, Columns.Count).End(xlToLeft).Offset(0, 1).Column
pasteRange.Cells(4, ecolumn).PasteSpecial xlPasteValues
Next
And when used inside the loop, it pastes data as mentioned above.
(2): I have placed the above For Loop inside Do-While loop in between two lines:
set wbk..............................
wbk.close..........................
I have noted that row in pasteRange can be changed by changing the number 4 in the above lines. But getting no idea to go to the next line after the data for one employee has been copied.
Please guide as early as possible. Thanks.
What does a looping process do? What would happen if you define the same column in a looping process? Think...
@@Exceltrainingvideos Sir it enters data in one column as:
Name:
Basic Pay:
House Rent:
Conveyance Allowance:
Medical Allowance
Name:
Basic Pay:
House Rent:
Conveyance Allowance:
Medical Allowance
I applied another approach.
Collected all data in a row as:
Name:
Basic Pay: House Rent: Conveyance Allowance: Medical Allowance Name: Basic Pay: House Rent: Conveyance Allowance: Medical Allowance
and then created another macro that follows as:
For i = 6 To 32
Sheets("sheet1").Activate
Range("B2:V2").Copy
'copy data about one employee
Sheets("sheet2").Activate
Sheets("sheet2").Range(Cells(i, "B"), Cells(i, "V")).Select
'This is the paste range
ActiveSheet.Paste
Sheets("sheet1").Activate
Range("B2: V2").EntireColumn.Delete
'After pasting, it deletes the copied cells from sheet1.
Next
Here I selected paste range as you have taught in your lecture about (Transfer Specific Worksheet Range Data from one worksheet to another based on condition).
Thank you so much for uploading lecture videos over such a diverse range of topics.