Results 1 to 7 of 7

Thread: How to copy many sheets in excel workbook to another sheet in another workbook?

  1. #1

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    How to copy many sheets in excel workbook to another sheet in another workbook?

    Dear All

    I am trying to copy a specific range from many sheets in excel workbook to another sheet in another workbook

    the problem it gives the data of the last sheet only

    Here is my code:

    Code:
    Sub CollectData()
    
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim I As Integer
    
    Application.DisplayAlerts = False
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("C:\Users\moheb.mikhail\Documents\Amr\Timesheet.xlsx")
    Set ws1 = wb1.Sheets("Jan")
    
    I = 0
     For Each ws2 In wb2.Sheets
     If Len(ws2.Name) > 0 Then
      ws2.Range("A2:G50").Copy Destination:=ws1.Range("A2:G50")
      End If
      I = I + 1
      Next ws2
    Application.DisplayAlerts = True
    wb2.Close (savechanges = True)
    
    End Sub
    Thanks your support

    Moheb Labib

  2. #2
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    Hello,

    Of course it does as you paste every thing in the same range , you need to shift your target range for each worksheet...
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  3. #3
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    by the way, do you know any worksheet who has a name of length 0???
    so this is useless :
    Code:
    If Len(ws2.Name) > 0 Then
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  4. #4
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    add something like that (not tested)
    vb.net Code:
    1. dim start as integer = 2
    2. dim finish as integer =50
    3. For Each ws2 In wb2.Sheets
    4.     ws2.Range("A2:G50").Copy Destination:=ws1.Range("A" & start.tostring & ":G" & finish.tostring)
    5.     start +=50
    6.     finish += 50
    7. Next ws2
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  5. #5

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    Dear Delany

    You are brilliant, Thanks a lot

  6. #6

    Thread Starter
    Banned
    Join Date
    Apr 2020
    Location
    https://t.me/pump_upp
    Posts
    61

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    Dear Delany

    Appreciate your support in my thread

    Function Works in Excel Macro But Does not Work in VB.Net

    I am sure you can solve it.

    Thanks, Regards

    Moheb Labib

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: How to copy many sheets in excel workbook to another sheet in another workbook?

    @meho
    I allready gave you an example in your other thread how to open a Workbook and access a sheet (your welcome by the way)

    where in this part you select the range to copy
    Code:
                With xlSt
                    'do stuff here with Sheet
                    'copy to diffrent worksheet
                    
                End With
    I could add Code to do what you want, but why not try yourself first,
    I had to when I started working with Excel and .NET
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width