Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Cleaning up some code?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Cleaning up some code?
Hi all,
a quick question on some code I have done for a macro. I have listed the
code below and basically the code had to be manually input for each sheet
that I wanted to move and the place in the new sheet had to be entered (1)
(2) (3) etc. Is there a way to enter this code so as that it will
automatically know where to put the sheets and not have to add in the
location manually.
The idea of the sheet is to rename each worksheet with the current file name
and then move the worksheet to another open book, the end result is all the
books become worksheets in the one book.
Any help much appreciated.
Patrick


Sheets("Austin April.xls").Move After:=Workbooks("Book3").Sheets(1)
Windows("Burr April.xls").Activate
Sheets("Burr April.xls").Move After:=Workbooks("Book3").Sheets(2)
Windows("Davis April.xls").Activate
Sheets("Davis April.xls").Move After:=Workbooks("Book3").Sheets(3)
Windows("Divall 2 April.xls").Activate
Sheets("Divall 2 April.xls").Move After:=Workbooks("Book3").Sheets(4)
Windows("Divall April.xls").Activate
Sheets("Divall April.xls").Move After:=Workbooks("Book3").Sheets(5)
Windows("Essen April.xls").Activate
Sheets("Essen April.xls").Move After:=Workbooks("Book3").Sheets(6)
Windows("Hill April.xls").Activate
Sheets("Hill April.xls").Move After:=Workbooks("Book3").Sheets(7)
Windows("Jackson April.xls").Activate
Sheets("Jackson April.xls").Move After:=Workbooks("Book3").Sheets(8)
Windows("Johnson April.xls").Activate
Sheets("Johnson April.xls").Move After:=Workbooks("Book3").Sheets(9)
Windows("Luna April.xls").Activate
Sheets("Luna April.xls").Move After:=Workbooks("Book3").Sheets(10)
Windows("McShane April.xls").Activate
Sheets("McShane April.xls").Move After:=Workbooks("Book3").Sheets(11)
Windows("Morrison April.xls").Activate
Sheets("Morrison April.xls").Move After:=Workbooks("Book3").Sheets(12)
Windows("Munguia April.xls").Activate
Sheets("Munguia April.xls").Move After:=Workbooks("Book3").Sheets(13)
Windows("Sauter April.xls").Activate
Sheets("Sauter April.xls").Move After:=Workbooks("Book3").Sheets(14)
Windows("Spencer April.xls").Activate
Sheets("Spencer April.xls").Move After:=Workbooks("Book3").Sheets(15)
Windows("Tran April.xls").Activate
Sheets("Tran April.xls").Move After:=Workbooks("Book3").Sheets(16)
Windows("Vargas April.xls").Activate
Sheets("Vargas April.xls").Move After:=Workbooks("Book3").Sheets(17)
Windows("Batchan April.xls").Activate
ActiveSheet.Name = ActiveWorkbook.Name
Sheets("Batchan April.xls").Move After:=Workbooks("Book3").Sheets(18)
Windows("Bobby April.xls").Activate
Sheets("Bobby April.xls").Move After:=Workbooks("Book3").Sheets(19)
Windows("Desboine April.xls").Activate
Sheets("Desboine April.xls").Move After:=Workbooks("Book3").Sheets(20)
Windows("Dillenburg April.xls").Activate
Sheets("Dillenburg April.xls").Move After:=Workbooks("Book3").Sheets(21)
Windows("Killenger April.xls").Activate
Sheets("Killenger April.xls").Move After:=Workbooks("Book3").Sheets(22)
Windows("Lawrence April.xls").Activate
Sheets("Lawrence April.xls").Move After:=Workbooks("Book3").Sheets(23)
Windows("Lopez April.xls").Activate
Sheets("Lopez April.xls").Move After:=Workbooks("Book3").Sheets(24)
Windows("Maloch April.xls").Activate
Sheets("Maloch April.xls").Move After:=Workbooks("Book3").Sheets(25)
Windows("Ochoa April.xls").Activate
Sheets("Ochoa April.xls").Move After:=Workbooks("Book3").Sheets(26)
Windows("Rogers April.xls").Activate
Sheets("Rogers April.xls").Move After:=Workbooks("Book3").Sheets(27)
Windows("Thomas April.xls").Activate
Sheets("Thomas April.xls").Move After:=Workbooks("Book3").Sheets(28)


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Cleaning up some code?
maybe something like this

Sheets("Austin April.xls").Move
After:=Workbooks("Book3").Worksheets(Worksheets.Co unt)

this would move it to after the last sheet


--


Gary


"Patrick" wrote in message
...
Hi all,
a quick question on some code I have done for a macro. I have listed the code
below and basically the code had to be manually input for each sheet that I
wanted to move and the place in the new sheet had to be entered (1) (2) (3)
etc. Is there a way to enter this code so as that it will automatically know
where to put the sheets and not have to add in the location manually.
The idea of the sheet is to rename each worksheet with the current file name
and then move the worksheet to another open book, the end result is all the
books become worksheets in the one book.
Any help much appreciated.
Patrick


Sheets("Austin April.xls").Move After:=Workbooks("Book3").Sheets(1)
Windows("Burr April.xls").Activate
Sheets("Burr April.xls").Move After:=Workbooks("Book3").Sheets(2)
Windows("Davis April.xls").Activate
Sheets("Davis April.xls").Move After:=Workbooks("Book3").Sheets(3)
Windows("Divall 2 April.xls").Activate
Sheets("Divall 2 April.xls").Move After:=Workbooks("Book3").Sheets(4)
Windows("Divall April.xls").Activate
Sheets("Divall April.xls").Move After:=Workbooks("Book3").Sheets(5)
Windows("Essen April.xls").Activate
Sheets("Essen April.xls").Move After:=Workbooks("Book3").Sheets(6)
Windows("Hill April.xls").Activate
Sheets("Hill April.xls").Move After:=Workbooks("Book3").Sheets(7)
Windows("Jackson April.xls").Activate
Sheets("Jackson April.xls").Move After:=Workbooks("Book3").Sheets(8)
Windows("Johnson April.xls").Activate
Sheets("Johnson April.xls").Move After:=Workbooks("Book3").Sheets(9)
Windows("Luna April.xls").Activate
Sheets("Luna April.xls").Move After:=Workbooks("Book3").Sheets(10)
Windows("McShane April.xls").Activate
Sheets("McShane April.xls").Move After:=Workbooks("Book3").Sheets(11)
Windows("Morrison April.xls").Activate
Sheets("Morrison April.xls").Move After:=Workbooks("Book3").Sheets(12)
Windows("Munguia April.xls").Activate
Sheets("Munguia April.xls").Move After:=Workbooks("Book3").Sheets(13)
Windows("Sauter April.xls").Activate
Sheets("Sauter April.xls").Move After:=Workbooks("Book3").Sheets(14)
Windows("Spencer April.xls").Activate
Sheets("Spencer April.xls").Move After:=Workbooks("Book3").Sheets(15)
Windows("Tran April.xls").Activate
Sheets("Tran April.xls").Move After:=Workbooks("Book3").Sheets(16)
Windows("Vargas April.xls").Activate
Sheets("Vargas April.xls").Move After:=Workbooks("Book3").Sheets(17)
Windows("Batchan April.xls").Activate
ActiveSheet.Name = ActiveWorkbook.Name
Sheets("Batchan April.xls").Move After:=Workbooks("Book3").Sheets(18)
Windows("Bobby April.xls").Activate
Sheets("Bobby April.xls").Move After:=Workbooks("Book3").Sheets(19)
Windows("Desboine April.xls").Activate
Sheets("Desboine April.xls").Move After:=Workbooks("Book3").Sheets(20)
Windows("Dillenburg April.xls").Activate
Sheets("Dillenburg April.xls").Move After:=Workbooks("Book3").Sheets(21)
Windows("Killenger April.xls").Activate
Sheets("Killenger April.xls").Move After:=Workbooks("Book3").Sheets(22)
Windows("Lawrence April.xls").Activate
Sheets("Lawrence April.xls").Move After:=Workbooks("Book3").Sheets(23)
Windows("Lopez April.xls").Activate
Sheets("Lopez April.xls").Move After:=Workbooks("Book3").Sheets(24)
Windows("Maloch April.xls").Activate
Sheets("Maloch April.xls").Move After:=Workbooks("Book3").Sheets(25)
Windows("Ochoa April.xls").Activate
Sheets("Ochoa April.xls").Move After:=Workbooks("Book3").Sheets(26)
Windows("Rogers April.xls").Activate
Sheets("Rogers April.xls").Move After:=Workbooks("Book3").Sheets(27)
Windows("Thomas April.xls").Activate
Sheets("Thomas April.xls").Move After:=Workbooks("Book3").Sheets(28)



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default Cleaning up some code?
Patrick,
I guess the easiest way would be to have a look up table of SourceWSNames
and IndexWSAfter. Then just loop through all the WS's in the WB:
Dim WS as Worksheet
For Each WS in Thisworkbook.Worksheets
WS.Move After
Workbooks("Book3").Sheets(Application.VlookUp(WS.N ame,rngDataLookUp,2,True))
Next

However, I doubt you a Sheet named "Austin April.xls", so I suspect you have
multiple workbooks open. Assuming it is the first worksheet of each WB you
wish to move:
Dim WB as Workbook
For Each WB in Workbooks
WB.Worksheets(1).Move After
Workbooks("Book3").Sheets(Application.VlookUp(WB.N ame,rngDataLookUp,2,True))
Next

Need some error trapping in case VLookUp fails.
Depending exactly what you are doing, the solution may be a combination the
above 2 routines.

NickHK

"Patrick" wrote in message
...
Hi all,
a quick question on some code I have done for a macro. I have listed the
code below and basically the code had to be manually input for each sheet
that I wanted to move and the place in the new sheet had to be entered (1)
(2) (3) etc. Is there a way to enter this code so as that it will
automatically know where to put the sheets and not have to add in the
location manually.
The idea of the sheet is to rename each worksheet with the current file
name
and then move the worksheet to another open book, the end result is all
the
books become worksheets in the one book.
Any help much appreciated.
Patrick


Sheets("Austin April.xls").Move After:=Workbooks("Book3").Sheets(1)
Windows("Burr April.xls").Activate
Sheets("Burr April.xls").Move After:=Workbooks("Book3").Sheets(2)
Windows("Davis April.xls").Activate
Sheets("Davis April.xls").Move After:=Workbooks("Book3").Sheets(3)
Windows("Divall 2 April.xls").Activate
Sheets("Divall 2 April.xls").Move After:=Workbooks("Book3").Sheets(4)
Windows("Divall April.xls").Activate
Sheets("Divall April.xls").Move After:=Workbooks("Book3").Sheets(5)
Windows("Essen April.xls").Activate
Sheets("Essen April.xls").Move After:=Workbooks("Book3").Sheets(6)
Windows("Hill April.xls").Activate
Sheets("Hill April.xls").Move After:=Workbooks("Book3").Sheets(7)
Windows("Jackson April.xls").Activate
Sheets("Jackson April.xls").Move After:=Workbooks("Book3").Sheets(8)
Windows("Johnson April.xls").Activate
Sheets("Johnson April.xls").Move After:=Workbooks("Book3").Sheets(9)
Windows("Luna April.xls").Activate
Sheets("Luna April.xls").Move After:=Workbooks("Book3").Sheets(10)
Windows("McShane April.xls").Activate
Sheets("McShane April.xls").Move After:=Workbooks("Book3").Sheets(11)
Windows("Morrison April.xls").Activate
Sheets("Morrison April.xls").Move After:=Workbooks("Book3").Sheets(12)
Windows("Munguia April.xls").Activate
Sheets("Munguia April.xls").Move After:=Workbooks("Book3").Sheets(13)
Windows("Sauter April.xls").Activate
Sheets("Sauter April.xls").Move After:=Workbooks("Book3").Sheets(14)
Windows("Spencer April.xls").Activate
Sheets("Spencer April.xls").Move After:=Workbooks("Book3").Sheets(15)
Windows("Tran April.xls").Activate
Sheets("Tran April.xls").Move After:=Workbooks("Book3").Sheets(16)
Windows("Vargas April.xls").Activate
Sheets("Vargas April.xls").Move After:=Workbooks("Book3").Sheets(17)
Windows("Batchan April.xls").Activate
ActiveSheet.Name = ActiveWorkbook.Name
Sheets("Batchan April.xls").Move After:=Workbooks("Book3").Sheets(18)
Windows("Bobby April.xls").Activate
Sheets("Bobby April.xls").Move After:=Workbooks("Book3").Sheets(19)
Windows("Desboine April.xls").Activate
Sheets("Desboine April.xls").Move After:=Workbooks("Book3").Sheets(20)
Windows("Dillenburg April.xls").Activate
Sheets("Dillenburg April.xls").Move
After:=Workbooks("Book3").Sheets(21)
Windows("Killenger April.xls").Activate
Sheets("Killenger April.xls").Move
After:=Workbooks("Book3").Sheets(22)
Windows("Lawrence April.xls").Activate
Sheets("Lawrence April.xls").Move After:=Workbooks("Book3").Sheets(23)
Windows("Lopez April.xls").Activate
Sheets("Lopez April.xls").Move After:=Workbooks("Book3").Sheets(24)
Windows("Maloch April.xls").Activate
Sheets("Maloch April.xls").Move After:=Workbooks("Book3").Sheets(25)
Windows("Ochoa April.xls").Activate
Sheets("Ochoa April.xls").Move After:=Workbooks("Book3").Sheets(26)
Windows("Rogers April.xls").Activate
Sheets("Rogers April.xls").Move After:=Workbooks("Book3").Sheets(27)
Windows("Thomas April.xls").Activate
Sheets("Thomas April.xls").Move After:=Workbooks("Book3").Sheets(28)




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Cleaning up some code?
One last point, is there a way to open a sheet directly into the current
open book? Currently I have the other workbook open and then move to the
main book. Can this be done?
Patrick

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
maybe something like this

Sheets("Austin April.xls").Move
After:=Workbooks("Book3").Worksheets(Worksheets.Co unt)

this would move it to after the last sheet


--


Gary


"Patrick" wrote in message
...
Hi all,
a quick question on some code I have done for a macro. I have listed the
code below and basically the code had to be manually input for each sheet
that I wanted to move and the place in the new sheet had to be entered
(1) (2) (3) etc. Is there a way to enter this code so as that it will
automatically know where to put the sheets and not have to add in the
location manually.
The idea of the sheet is to rename each worksheet with the current file
name and then move the worksheet to another open book, the end result is
all the books become worksheets in the one book.
Any help much appreciated.
Patrick


Sheets("Austin April.xls").Move After:=Workbooks("Book3").Sheets(1)
Windows("Burr April.xls").Activate
Sheets("Burr April.xls").Move After:=Workbooks("Book3").Sheets(2)
Windows("Davis April.xls").Activate
Sheets("Davis April.xls").Move After:=Workbooks("Book3").Sheets(3)
Windows("Divall 2 April.xls").Activate
Sheets("Divall 2 April.xls").Move After:=Workbooks("Book3").Sheets(4)
Windows("Divall April.xls").Activate
Sheets("Divall April.xls").Move After:=Workbooks("Book3").Sheets(5)
Windows("Essen April.xls").Activate
Sheets("Essen April.xls").Move After:=Workbooks("Book3").Sheets(6)
Windows("Hill April.xls").Activate
Sheets("Hill April.xls").Move After:=Workbooks("Book3").Sheets(7)
Windows("Jackson April.xls").Activate
Sheets("Jackson April.xls").Move After:=Workbooks("Book3").Sheets(8)
Windows("Johnson April.xls").Activate
Sheets("Johnson April.xls").Move After:=Workbooks("Book3").Sheets(9)
Windows("Luna April.xls").Activate
Sheets("Luna April.xls").Move After:=Workbooks("Book3").Sheets(10)
Windows("McShane April.xls").Activate
Sheets("McShane April.xls").Move After:=Workbooks("Book3").Sheets(11)
Windows("Morrison April.xls").Activate
Sheets("Morrison April.xls").Move After:=Workbooks("Book3").Sheets(12)
Windows("Munguia April.xls").Activate
Sheets("Munguia April.xls").Move After:=Workbooks("Book3").Sheets(13)
Windows("Sauter April.xls").Activate
Sheets("Sauter April.xls").Move After:=Workbooks("Book3").Sheets(14)
Windows("Spencer April.xls").Activate
Sheets("Spencer April.xls").Move After:=Workbooks("Book3").Sheets(15)
Windows("Tran April.xls").Activate
Sheets("Tran April.xls").Move After:=Workbooks("Book3").Sheets(16)
Windows("Vargas April.xls").Activate
Sheets("Vargas April.xls").Move After:=Workbooks("Book3").Sheets(17)
Windows("Batchan April.xls").Activate
ActiveSheet.Name = ActiveWorkbook.Name
Sheets("Batchan April.xls").Move After:=Workbooks("Book3").Sheets(18)
Windows("Bobby April.xls").Activate
Sheets("Bobby April.xls").Move After:=Workbooks("Book3").Sheets(19)
Windows("Desboine April.xls").Activate
Sheets("Desboine April.xls").Move After:=Workbooks("Book3").Sheets(20)
Windows("Dillenburg April.xls").Activate
Sheets("Dillenburg April.xls").Move
After:=Workbooks("Book3").Sheets(21)
Windows("Killenger April.xls").Activate
Sheets("Killenger April.xls").Move
After:=Workbooks("Book3").Sheets(22)
Windows("Lawrence April.xls").Activate
Sheets("Lawrence April.xls").Move After:=Workbooks("Book3").Sheets(23)
Windows("Lopez April.xls").Activate
Sheets("Lopez April.xls").Move After:=Workbooks("Book3").Sheets(24)
Windows("Maloch April.xls").Activate
Sheets("Maloch April.xls").Move After:=Workbooks("Book3").Sheets(25)
Windows("Ochoa April.xls").Activate
Sheets("Ochoa April.xls").Move After:=Workbooks("Book3").Sheets(26)
Windows("Rogers April.xls").Activate
Sheets("Rogers April.xls").Move After:=Workbooks("Book3").Sheets(27)
Windows("Thomas April.xls").Activate
Sheets("Thomas April.xls").Move After:=Workbooks("Book3").Sheets(28)





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Cleaning up Code Lost and Looking for Help Excel Programming 1 May 24th 06 02:26 PM
Help on cleaning / speeding up code Chris Salcedo Excel Programming 2 October 17th 05 01:16 AM
Need help Cleaning up Code Joel Mills Excel Programming 5 December 16th 04 10:36 PM
Help with shortening/cleaning some code please roy Excel Programming 3 June 3rd 04 11:49 PM
VBA Code -- Cleaning Data Craig[_8_] Excel Programming 2 December 12th 03 12:21 AM


All times are GMT +1. The time now is 12:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement