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

 

[Jonmo1]

Does Jan.xls Already have the sheets TO1 - T038 ? Or does the macro need to create them?

 

[CThai]

the macro will need to create them... Jan.xls is a new workbook

 

[ADAMC]

So you want a macro to create a new workbook called Jan.xls
Then from the master you want to your range from T01 and paste it to sheet 1 of the new workbook.
Then you want the range of T03 copied and pasted to sheet 2 of a new workbook?
Etc etc...?

 

[CThai]

ADAMC-

Yes, I want the marco to create a new workbook called Jan.xls
Copy T01 range A3:N59 from Master - and paste it to sheet 1 in Jan.xls and then copy T03 range D3:P50 from Master - and paste it to sheet 2 in Janxls... ect...

but i dont want the worksheets to be name sheets 1, sheets 2... can it be name T01, T03..ect

 

[ADAMC]

Given the fact we dont know all your ranges i think you would be better to use the macro recorder:
Tools > Macros > Record new macro

Then record your actions:
Do file new workbook save it as Jan.xls
rename sheet 1 to T01
copy your range from the other sheet T01 and paste to Jan.xls sheet 1
Right click on any tab in Jan.xls and do insert new worksheet.

Stop the macro recorder....You should now have all the code you need to suit what you require.
You have the code to create a new workbookcalled Jan.xls
You have all the copy and paste code and you have the code to add a new worksheet.

You can then adapt it to suit your needs, perhaps once you do this you can post the code and it could be tidied up for you.

I think this might be a good way to go however one of the experts in this thread might be able to come up with something.

 

[Jonmo1]

Ok, try this out.
2 macros,
CreateSheets - creates the sheets
copyranges - copies the ranges.

This assumes you have both workbooks open. Master.xls and Jan.xls

Sub CreateSheets()
'This Macro Creates the sheets - only needs to be run once.
'
'
Workbooks("Jan.xls").Activate
For x = 1 To 58
Sheets.Add
ActiveSheet.Name = "T" & format(x, "0#")
Next x
End Sub


Sub copyranges()
Workbooks("Master.xls").Activate
For x = 1 To 58
    Sheets("T" & format(x, "0#")).Select
    Set myrange = Application.InputBox(prompt:="Select Range", Type:=8)
    myrange.Copy Workbooks("Jan.xls").Sheets("T" & format(x, "0#")).Range("A1")
Next x
End Sub

 

[CThai]

hi jonmo1

the first code work..but it's giving me the order of the workbook from 58-1 i need it to be from 1-58....

the second code is giving me an error

"Run-time error '9'"

subscript out of range..

I have both workbooks open... do i need to copy the second code into Master?

thanks

 

[Jonmo1]

1.
change
For x = 1 To 58
to
For x = 58 To 1 step -1

2.
Which line is highlighted when you click debug?

 

[CThai]

this line

Workbooks("Master.xls").Activate

 

[Jonmo1]

The only reason that would fail is if there is no such book named "Master.xls" currently open. Verify the spelling on the workbook's name, look for leading/trailing spaces.