Posts by mylai

Re: VBA - importing all excel files from subfolders

HI,

When I merge multiple workbooks from different sub folder by using below code, the header gets repeated, e.g column A1 to AQ15. How do I remove the header and merge only the data file.

Sub M_snb() sn=filter(split(Createobject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.xls"" /b/s").stdout.readall,vbcrlf),":")

For j=0 To UBound(sn)
With getobject(sn(j)).sheets(1).usedrange
thisworkbook.sheets(1).cells(rows.count,1).end(xlup).offset(2).resize(.rows.count,.columns.count)=.value
.Parent.parent.close 0
End With
Next
End Sub

Hi,

When I create a named range in excel by using offset function and trying to get total sales, it doesn't automatically include new items if there is blank column in between, is there any way to solve it?

https://www.excelforum.com/exc…ce-for-dynamic-range.html

Re: how to disable inserting columns in excel by using VBA

Excellent! Thanks for your help

Hi, I have VB Coding as below but the problem is when I using below code , it disable other functions as well - Format Cells, Column Width , Hide, Unhide and freeze panes. Is there any solution to disable column function only by using VB code for all worksheets in all workbooks in one folder?

[COLOR=#333333]Private Sub Workbook_SheetActivate(ByVal Sh As Object)[/COLOR]


[COLOR=#333333]'ProtectSheet[/COLOR]
[COLOR=#333333]ActiveSheet.Protect AllowInsertingColumns:=False, _[/COLOR]
[COLOR=#333333]AllowInsertingRows:=True, _[/COLOR]
[COLOR=#333333]AllowDeletingColumns:=True, _[/COLOR]
[COLOR=#333333]AllowDeletingRows:=True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]