copy range from different worksheets

[CThai]

Hi -

I have a workbook with 58 worksheets in it... each worksheets is name T01, T02....T58, I need to copy a range from different worksheets...

I don't need all the worksheets in Master.xls I only need some of the worksheets...

for example :

I need -
T01 - range from A3:N59
T03 - range from D3:P50
T05 - range from D4:O60
T07- range from D3:N72
T08 range from B3:N55
ect...


I would like to copy the Value and format ...

please help

thank you

Cthai

 

[CThai]

ok the sheets that i want to exclude are T02, T04, T06, T08, T10, T12, T14, T16, T18, T20, T22, T24, T36a, T36b, T38a, T38a, T38c, T43a,T43b,T43c,T43d, T44a-c, T45a-c, T46a-c, T48, T50a-c, T52a-c, TC1a, TC1b, Append D, Home, Table, Data, Data_M, Data_TD...

those are the one that I want to be EXCLUDED.

 

[Jonmo1]

without the list of excluded sheets, here's what I have...

again 2 macros, 1 to create the sheets, 2nd to do the copying..
still assuming both books are open.

the line
myarray = Array("sheet1", "sheet2", "sheet3")

that is where you put the sheets you want excluded

Sub createsheets()
Workbooks("Jan.xls").Activate
myarray = Array("sheet1", "sheet2", "sheet3")
For Each Sheet In Workbooks("master.xls").Sheets
    x = "Not Found"
    On Error Resume Next
    x = WorksheetFunction.Lookup(Sheet.Name, myarray)
    If x <> "Not Found" Then
        Sheets.Add
        ActiveSheet.Name = Sheet.Name
    End If
Next Sheet
End Sub


Sub copyranges()
Workbooks("Master.xls").Activate
For Each Sheet In Sheets
    Sheet.Select
    Set myrange = Application.InputBox(prompt:="Select Range to copy from sheet " & Sheet.Name, Type:=8)
    myrange.Copy Workbooks("Jan.xls").Sheets(Sheet.Name).Range("a1")
Next Sheet
End Sub

 

[CThai]

i put in the sheets that i dont want and it's still creating the sheets name in Jan.xls...

Also, when i run the second code it's bring me to the "Home" worksheets to copy the range - i have the T01 worksheets as the active worksheets...

 

[CThai]

one more thing - the worksheets created in Jan.xls is again 58-1... can i have it create from 1-58

 

[Jonmo1]

Sub createsheets() 
Workbooks("Jan.xls").Activate 
myarray = Array("sheet1", "sheet2", "sheet3") 
For Each Sheet In Workbooks("master.xls").Sheets 
    x = "Not Found" 
    On Error Resume Next 
    x = WorksheetFunction.Lookup(Sheet.Name, myarray) 
    If x <> "Not Found" Then 
        Sheets.Add 
        ActiveSheet.Name = Sheet.Name 
        ActiveSheet.Move Before:=Sheets(1)
    End If 
Next Sheet 
End Sub

 

[Jonmo1]

Ok, I think this is going to do it..

again, you need to update the line
myarray = array("Sheet1",...)

with your sheets to exclude..

Sub createsheets()
Workbooks("Jan.xls").Activate
myarray = Array("Sheet1", "Sheet2", "Sheet3", "TESTING")
For Each Sheet In Workbooks("master.xls").Sheets
    x = "Not Found"
    For i = LBound(myarray) To UBound(myarray)
        If Sheet.Name = myarray(i) Then x = "Found"
    Next i
    If x = "Not Found" Then
        Sheets.Add
        ActiveSheet.Name = Sheet.Name
        ActiveSheet.Move Before:=Sheets(1)
    End If
Next Sheet
End Sub


Sub copyranges()
Workbooks("Master.xls").Activate
For Each Sheet In Workbooks("Jan.xls").Sheets
    Sheets(Sheet.Name).Select
    Set myrange = Application.InputBox(prompt:="Select Range to copy from sheet " & Sheet.Name, Type:=8)
    myrange.Copy Workbooks("Jan.xls").Sheets(Sheet.Name).Range("a1")
Next Sheet
End Sub

 

[CThai]

Hi jonmo1 -

the codes worked great! Thank you soo much, just one quest, i want to know if it's possible to have a message box to let me select the worksheets to be copy?

I really appreciate all your help...

thanks a bunch!!

 

[CThai]

hi jonmo1 -

the codes work wonderfully! but the worksheets still starts with 58... is there a way i can make it starts with 1? thanks a bunch!

 

[CThai]

hi jonmo1-

I figure out what i needed to change to make it copy from 1-58 instead of 58-1


i just modify

ActiveSheet.Move Before:=Sheet(1)[\code]

to this

[code]ActiveSheet.Move Before:=Sheet1[\code]

thank you for helping me with this... your a life saver!!

 

[CThai]

hi jonmo1-

I figure out what i needed to change to make it copy from 1-58 instead of 58-1


i just modify

ActiveSheet.Move Before:=Sheet(1)[\code]

to this

[code]ActiveSheet.Move Before:=Sheet1[\code]

thank you for helping me with this... your a life saver!!