copy multiple worksheet data from 2 different workbook

[Ita]

Hi,
How to translate the following into a working code in the following
scenario. The code will run in new workbook :-
1.From new workbook, open old workbook. To use
Application.GetOpenFilename(filefilter:="Excel Files, *.xl*")
2.If sheet name end with ABC or –AB, copy range (J5:N & last row) from old
workbook to new workbook
3.If sheet name end with XYZ or -YZ, copy range (M5:R & last row) from old
workbook to new workbook
The worksheets are in alphabetical order (and same name). I want to copy the
values only from
old book - workbook(1).worksheet(1).range to
new book - workbook(2).worksheet(1).range
There are a total of 18 worksheets to copy from old workbook to new workbook.

Thank you.

 

[Joel]

try this code

Sub GetData()

Set NewBkSht = ThisWorkbook.Sheets(1)
NewBkSht.Name = "Summary"
NewBkSht.Cells.ClearContents

filetoopen = Application _
.GetOpenFilename("Text Files (*.xl*), *.xl*")
If filetoopen = False Then
MsgBox ("cannot open file - Exitting Macro")
Exit Sub
End If

Set OldBk = Workbooks.Open(Filename:=filetoopen)

For Each sht In OldBk.Sheets
NewLastRow = NewBkSht.Range("A" & Rows.Count).End(xlUp).Row
If UCase(Right(sht.Name, 3)) = "ABC" Then
OldLastRow = sht.Range("J" & Rows.Count).End(xlUp).Row
sht.Range("J5:N" & OldLastRow).Copy _
Destination:=NewBkSht.Range("A" & (NewLastRow + 1))
End If
If UCase(Right(sht.Name, 3)) = "-AB" Then
OldLastRow = sht.Range("M" & Rows.Count).End(xlUp).Row
sht.Range("M5:R" & OldLastRow).Copy _
Destination:=NewBkSht.Range("A" & (NewLastRow + 1))
End If
Next sht

OldBk.Close savechanges:=False

End Sub

 

[Ita]

Hi Joel,
Thank you for the reply but it doesn't seems to work.
What I mean is to copy value from sheet(1) to sheet(1), from sheet(2) to
sheet(2) something like
copy oldbk(1).sheet(1).range to new bk(2).sheet(1).range and then
copy oldbk(1).sheet(2).range to new bk(2).sheet(2).range
as for range to copy depends on the sheetname

 

[Joel]

Sorry for the problem. Most people who are doing similar type macros are
looking for a summary sheet. You didn't specify where the data needed to
go. I just copied the data you asked for to the same location on the new
sheet in the code below. I also only copied the sheets with ABC or -ABC at
the ednd and ignored other sheets.


Sub GetData()

filetoopen = Application _
.GetOpenFilename("Text Files (*.xl*), *.xl*")
If filetoopen = False Then
MsgBox ("cannot open file - Exitting Macro")
Exit Sub
End If

Set OldBk = Workbooks.Open(Filename:=filetoopen)

For Each sht In OldBk.Sheets
With ThisWorkbook
Set NewSht = .Sheets.Add(after:=.Sheets(.Sheets.Count))
NewSht = sht.Name

If UCase(Right(sht.Name, 3)) = "ABC" Then
OldLastRow = sht.Range("J" & Rows.Count).End(xlUp).Row
sht.Range("J5:N" & OldLastRow).Copy _
Destination:=NewSht.Range("J5")
End If
If UCase(Right(sht.Name, 3)) = "-AB" Then
OldLastRow = sht.Range("M" & Rows.Count).End(xlUp).Row
sht.Range("M5:R" & OldLastRow).Copy _
Destination:=NewBkSht.Range("M5")
End If
End With
Next sht

OldBk.Close savechanges:=False

End Sub

 

[ahmed abd elemgeed]

How are you

About Creating a series of sheets based on a master sheet (#master)
At the sheet1 I write from B1 to B10 lp1, lp2,lp3,…….till lp10 and create CommandButton1 And use this following operation to make copy of sheet master with new names as lp1, lp2,lp3,…….till lp10

Private Sub CommandButton1_Click()

Dim i As Long

For i = 1 To 50

If Cells(i, 2) = 0 Then

Exit For

Else

Sheets("Master").Copy After:=Sheets(Sheets.Count)

Sheets("Master (2)").Name = Cells(i, 2)

End If

Next i

End Sub





After I apply the previous Creating a series of sheets based on a master sheet (#master) with CommandButton1 at second press I have error

The question how I can avoid this error at the second press (for example someone press by error????

The second question is if I write at the cells B11,B15

New text lp11,lp12, what is the function added to previous function to make after written or adding lp11,lp12 the excel vba under stand insert new sheets lp11,lp12 added to lp1 to lp10 sheets and so on if I ad any names of sheets in column by and press button the new names sheets while added?????

 

[Joel]

Try these changes

Private Sub CommandButton1_Click()

Dim i As Long


For i = 1 To 50

If sheets("Sheet1").Cells(i, "B") = 0 Then

Exit For

Else

set newsht = Sheets("Master").Copy( After:=Sheets(Sheets.Count) )

newsht.Name = sheets("Sheet1").Cells(i, 2)

End If

Next i

End Sub