Results 1 to 10 of 10

Required vba code copy data sheets to new workbook & retn to old sheet userform

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-16-2018
    Location
    India
    MS-Off Ver
    2021
    Posts
    177

    Required vba code copy data sheets to new workbook & retn to old sheet userform

    Dear Sir,

    I want to required vba code copy data from sheets to new workbook .
    for exp ;
    1. listbox1.value select option 1 then copy sheet1 to new workbook
    2. listbox1.value select option 2 then copy sheet2 to new workbook
    3. listbox1.value select option 3 then copy sheet3 to new workbook

    when complete the task , then goto return userform1 .

    thank you all..

    listbox.png

    Option Explicit
    
    Private Sub CommandButton1_Click()
        Dim wbI As Workbook, wbO As Workbook
        Dim wsI As Worksheet, wsO As Worksheet
        
    If ListBox1.Value = "COPY SHEET 1 TO NEW WORKBOOK" Then
        Set wbI = ThisWorkbook
        Set wsI = wbI.Sheets("Sheet1")
        Set wbO = Workbooks.Add
        With wbO
            Set wsO = wbO.Sheets("Sheet1")
            'Save the file
            '.SaveAs Filename:="C:\Book2.xls", FileFormat:=56
    
            'wsI.Range("A1:B10").Copy
             wsI.Cells.Copy
             wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False
            
             Application.ScreenUpdating = True
         End With
    ElseIf ListBox1.Value = "COPY SHEET 2 TO NEW WORKBOOK" Then
        Set wsI = wbI.Sheets("Sheet2")
        Set wbO = Workbooks.Add
        With wbO
            Set wbI = ThisWorkbook
             Set wsO = wbO.Sheets("Sheet1")
            'Save the file
            '.SaveAs Filename:="C:\Book2.xls", FileFormat:=56
    
            'wsI.Range("A1:B10").Copy
             wsI.Cells.Copy
             wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False
            
             Application.ScreenUpdating = True
         End With
    ElseIf ListBox1.Value = "COPY SHEET 3 TO NEW WORKBOOK" Then
        Set wbI = ThisWorkbook
        Set wsI = wbI.Sheets("Sheet3")
        Set wbO = Workbooks.Add
        With wbO
             Set wsO = wbO.Sheets("Sheet1")
            'Save the file
            '.SaveAs Filename:="C:\Book2.xls", FileFormat:=56
    
            'wsI.Range("A1:B10").Copy
             wsI.Cells.Copy
             wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=False
            
             Application.ScreenUpdating = True
        
        End With
    End If
    End Sub
    
    Private Sub CommandButton2_Click()
    Unload Me
    End Sub
    
    Private Sub UserForm_Initialize()
    
    With ListBox1
        .AddItem "COPY SHEET 1 TO NEW WORKBOOK"
        .AddItem "COPY SHEET 2 TO NEW WORKBOOK"
        .AddItem "COPY SHEET 3 TO NEW WORKBOOK"
    End With
    
    End Sub

    error.png
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] amend code copy data from userform to sheet before lastrow
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-14-2020, 01:05 PM
  2. Userform Code Required to Control Data between Database and Input Sheet
    By Rudidw in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2020, 01:15 AM
  3. [SOLVED] vba code to paste data from different sheets to one single sheet in same workbook
    By Ishwarind in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2016, 02:39 AM
  4. VBA COde For copy and paste B column of every sheets to new workbook in one sheet
    By Chetan_raghu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2016, 02:22 PM
  5. [SOLVED] VBA code required to insert extra/New sheets with existing sheet data
    By mchilapur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2015, 01:52 AM
  6. Code Required To Use Condition To Copy Data From Different Worksheets Into Another Sheet
    By lukerogers1987 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2012, 05:26 AM
  7. Replies: 0
    Last Post: 08-11-2011, 01:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1