-
Sep 22nd, 2020, 07:46 AM
#1
Thread Starter
Banned
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
-
Sep 22nd, 2020, 08:12 AM
#2
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)
-
Sep 22nd, 2020, 08:14 AM
#3
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)
-
Sep 22nd, 2020, 08:18 AM
#4
Re: How to copy many sheets in excel workbook to another sheet in another workbook?
add something like that (not tested)
vb.net Code:
dim start as integer = 2 dim finish as integer =50 For Each ws2 In wb2.Sheets ws2.Range("A2:G50").Copy Destination:=ws1.Range("A" & start.tostring & ":G" & finish.tostring) start +=50 finish += 50 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)
-
Sep 22nd, 2020, 08:29 AM
#5
Thread Starter
Banned
Re: How to copy many sheets in excel workbook to another sheet in another workbook?
Dear Delany
You are brilliant, Thanks a lot
-
Nov 15th, 2020, 07:51 AM
#6
Thread Starter
Banned
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
-
Nov 15th, 2020, 09:19 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|