VBA a code review and help for Copy pasting data form 1 to another excel

[sudhansu121]

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.

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 ?

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA a code review and help for Copy pasting data form 1 to another excel
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[sudhansu121]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA a code review and help for Copy pasting data form 1 to another excel
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem

Hi RoryA

I have now removed marked the other one as solved(did not find a solution to it so.) .

 

[RoryA]

If you actually read the rules, you will see that cross-posting is absolutely fine as long as you provide the links.

 

[sudhansu121]

If you actually read the rules, you will see that cross-posting is absolutely fine as long as you provide the links.

From nest time I will keep this in mind. Thanks dear.

 

[smozgur]

@sudhansu121 - "Mark as Solution" is not "Close this thread" equivalent and there is no thread closure in the MrExcel Message Board. If there is no solution in a thread, then leave it without marking a post as the solution.

Please read this for more details.

 

[sudhansu121]

I have now find out the solution.

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
  

With wb.Worksheets("Missing Visit").Cells(2, 3).Resize(wb.Worksheets("Missing Visit").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 wb.Worksheets("Missing item").Cells(2, 3).Resize(wb.Worksheets("Missing item").UsedRange.Rows.Count, 1)
'.AutoFilter field:=4, Criteria1:="Missing item"
.Columns(1).SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Worksheets(""Missing item"").Cells(2, 1).PasteSpecial xlPasteValues
End With

  
With ThisWorkbook.Worksheets(""Missing item" ").Columns(1)
.NumberFormat = "General"
.Value = .Value
End With
  
wb.Close False
End Sub

And it is working as expected.