Hi All,
I am facing one challenge and need help regarding the excel vba. I have a macro (abc.xlsm) which will open an excel file (bca.xlsx) and on that excel file (bca.xlsx) so many worksheets are present. The macro has one sheet called "MDM" and when the macro will open the excel file (bca.xlsx), from that excel file (bca.xlsx) it will copy M column data from all sheets one by one and paste on the "MDM" sheet vertically. Here in my case the below code is not giving any error but it is not looping through all the worksheets and copy data. Please help me on this regard. Thank you.
Regards,
Pinaki
I am facing one challenge and need help regarding the excel vba. I have a macro (abc.xlsm) which will open an excel file (bca.xlsx) and on that excel file (bca.xlsx) so many worksheets are present. The macro has one sheet called "MDM" and when the macro will open the excel file (bca.xlsx), from that excel file (bca.xlsx) it will copy M column data from all sheets one by one and paste on the "MDM" sheet vertically. Here in my case the below code is not giving any error but it is not looping through all the worksheets and copy data. Please help me on this regard. Thank you.
VBA Code:
Sub WAH()
Dim MyTargetFile As Variant
Dim MyAnotherSourceFile As Variant
Dim ws As Worksheet
MyTargetFile = "abc.xlsm"
MyAnotherSourceFile = Application.GetOpenFilename("Excel Files (*.xlsx),(*.xls), *xlsx,*.xls")
Workbooks.Open Filename:=MyAnotherSourceFile
SourceFileTempName = ActiveWorkbook.Name
For Each ws In Worksheets
Range("M3").End(xlDown).Select
Selection.Copy
Workbooks(MyTargetFile).Sheets("MDM").Activate
ActiveCell.PasteSpecial
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
Next ws
End Sub
Regards,
Pinaki