sudhansu121
New Member
- Joined
- Jan 19, 2021
- Messages
- 28
- Office Version
- 365
Hi Experts,
I have a code which I have gathered by doing some research in Google and this working fine for me . Suddenly the requirements have been changed and need to modify the code accordingly.
I use this to select a workbook with only worksheet and then copy the column 3 data into my master macro sheet.
Now we have 1 master macro sheet and 1 Workbook with multiple sheets.
I want to run the macro and it should go to the Workbook and select the name of the worksheet (as per the macro name) and copy paste the data in the master workbook.
Could you please review the code and suggest ?
I have a code which I have gathered by doing some research in Google and this working fine for me . Suddenly the requirements have been changed and need to modify the code accordingly.
VBA Code:
Sub MissingVisit()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks.Open(Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx"))
On Error GoTo 0
If wb Is Nothing Then
MsgBox "File selection was cancelled." & vbCrLf & vbCrLf & "Exiting...", , "No File Selected"
Exit Sub
End If
ThisWorkbook.Worksheets("Missing Visit ").Rows("2:" & Rows.Count).ClearContents
Worksheets("Missing Visits").Activate
With wb.Worksheets(1).Cells(2, 3).Resize(wb.Worksheets(1).UsedRange.Rows.Count, 1)
'.AutoFilter field:=4, Criteria1:="Missing item"
.Columns(1).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets("Missing Visit ").Cells(2, 1).PasteSpecial xlPasteValues
End With
With ThisWorkbook.Worksheets("Missing Visit ").Columns(1)
.NumberFormat = "General"
.Value = .Value
End With
wb.Close False
End Sub
I use this to select a workbook with only worksheet and then copy the column 3 data into my master macro sheet.
Now we have 1 master macro sheet and 1 Workbook with multiple sheets.
I want to run the macro and it should go to the Workbook and select the name of the worksheet (as per the macro name) and copy paste the data in the master workbook.
Could you please review the code and suggest ?