Feb 20 2020 02:54 AM
Dear Reader,
I work on some handful of excels everyday. For some files I need to fill Column A until multiple or single data available in Column B(usually from B2 the datas will be available as B1 could be a header row). and if no data available in a sheet the macro should move for the next file without throwing any error. what would be the code for the same. got failed with multiple codes.
Thanks in advance for your assistance.
Feb 20 2020 03:04 AM
Are you saying that one or multiple excel files are opened and you want to fill column A based on what is filled in column B?
What is to be filled in column A?
Isn't it better if you upload a sample file to show us what exactly you are trying to achieve by mocking up the desired output manually in column A?
Feb 21 2020 12:21 AM
Feb 21 2020 12:45 AM
The following code will loop through all the worksheets of the open workbooks and fill the column A with what is there in cell A2 on those sheets.
Sub FillColumnA()
Dim wb As Workbook
Dim ws As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
For Each wb In Application.Workbooks
For Each ws In wb.Worksheets
lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
ws.Range("A2:A" & lr).Value = ws.Range("A2").Value
Next ws
Next wb
Application.ScreenUpdating = True
End Sub