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

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
23
Office Version
  1. 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.

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 ?
 

Attachments

  • sheets.PNG
    sheets.PNG
    7.9 KB · Views: 3

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Jan 19, 2021
Messages
23
Office Version
  1. 365
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

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you actually read the rules, you will see that cross-posting is absolutely fine as long as you provide the links.
 

sudhansu121

New Member
Joined
Jan 19, 2021
Messages
23
Office Version
  1. 365

ADVERTISEMENT

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

BatCoder
Joined
Feb 28, 2002
Messages
1,403
@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

New Member
Joined
Jan 19, 2021
Messages
23
Office Version
  1. 365
I have now find out the solution.

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
  

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.
 
Last edited by a moderator:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,476
Messages
5,636,556
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top