Import data from Workbook1 to Workbook2 from multiple sheets to multiple sheets

oyu0217

New Member
Joined
Jan 7, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hey peeps, how do I insert a similar process after the first one?

The process is to import from Source workbook1 - sheet1 to Destination workbook2 to sheet1 then next Source workbook1 - sheet2 to Destination workbook2 to sheet2 four more times...

Here's what I have so far...

VBA Code:
Sub ImportAllData()

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
        Application.ScreenUpdating = False

    FileToOpen = Application.GetOpenFilename(Title:="Select the file", FileFilter:="Excel Files (*.xlsm),*.xlsm")
    
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        
        'From Workbook1.Sheet1 to Workbook2.Sheet1
        OpenBook.Sheets(1).Select
        Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        'To Workbook2 to Sheet1
        Windows("DestinationWorkbook.xlsm").Activate
        Sheets("Question Form Data").Select
        Range("QuestionTable[[#Headers],['#]]").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("QuestionTable[[#Headers],['#]]").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("QuestionTable[#All]").RemoveDuplicates Columns:=Array(1, 2, _
            3, 4, 5), Header:=xlYes
        
        'Insert next step
        'There's an error in this partI tried NEXT, THEN, CALL but not sure what is the appropriate command to do the next

        'From Workbook1 to Sheet2 to Workbook2.Sheet2
        OpenBook.Select
        OpenBook.Sheets(3).Select
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        'To Workbook2.Sheet2
        Windows("LeadershipTracker_2022 POC test .1.xlsm").Activate
        Sheets("DSAT callback form data").Select
        Range("DSATcallbackTable[[#Headers],['#]]").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("DSATcallbackTable[[#Headers],['#]]").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("DSATcallbackTable[#All]").RemoveDuplicates Columns:=Array( _
            1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11), Header:=xlYes
        
        'Insert FOUR MORE or FIVE same process

        'ActiveWorkbook.Save
        OpenBook.Close False


    End If
        Application.ScreenUpdating = True
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

oyu0217

New Member
Joined
Jan 7, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
All good now. :) Loop is the key (A friend of mine helped me on this)
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,981
Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
 

oyu0217

New Member
Joined
Jan 7, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
Its still in progress ?
 

Forum statistics

Threads
1,181,607
Messages
5,930,884
Members
436,764
Latest member
avalladarez

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