Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code Improvements
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default Code Improvements
I have the following code, I know there has to be some way to improve on it.
I would greatly appreciate it if someone would give me some guidance on it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 301
Default Code Improvements
Sub test()
Dim wb2 as worksheet
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")
Set wb2=workbooks("Book2").Sheets("Sheet1")

With wb1.Worksheets("txtMaster")
.Range("C1:C1500").Copy wb2.range("B3")
.Range("F1:F1500").Copy wb2.range("C3")
.Range("G1:G1500").Copy wb2.range("D3")
.Range("I1:I1500").Copy wb2.range("E3")
.Range("J1:J1500").Copy wb2.range("F3")
.Range("K1:K1500").Copy wb2.range("G3")
.Range("M1:M1500").Copy wb2.range("H3")
End With
End Sub

Bob Umlas

"Larry" wrote in message
...
I have the following code, I know there has to be some way to improve on
it.
I would greatly appreciate it if someone would give me some guidance on
it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 136
Default Code Improvements
Hi Larry,

I suggest to "steal" some code:
http://www.erlandsendata.no/english/...batextimportwb

HTH,
Bernd

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Code Improvements
see if this will do what you want. you can still use your rng variables if you
want, i just used the explicit ranges

Sub test()
Dim wb2 As Worksheet
Dim wb1 As Workbook
Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")
Set wb2 = Workbooks("Book2").Sheets("Sheet1")

With wb1.Worksheets("txtMaster")
Union(.Range("C1:C1500"), .Range("F1:F1500"), .Range("G1:G1500"), _
.Range("I1:I1500"), .Range("J1:J1500"), .Range("K1:K1500"), _
.Range("M1:M1500")).Copy wb2.Range("B3")
End With
End Sub

--


Gary


"Larry" wrote in message
...
I have the following code, I know there has to be some way to improve on it.
I would greatly appreciate it if someone would give me some guidance on it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default Code Improvements
Here's an additional idea, given your exact columns:

Sub Demo()
Dim RngFrom As Range
Dim RngTo As Range
Dim C As Long '(C)olumn
Dim T As Long '(T)emp

Set RngFrom = Workbooks("xlMasterFile.xls"). _
Worksheets("txtMaster"). _
Range("C1:M1500")

Set RngTo = Workbooks("Book2.xls"). _
Sheets("Sheet1"). _
Range("B3:H3")

For C = 1 To 7
'Adjuct Column number:
T = 155767382 Mod (C * 6 + 1)
RngFrom.Columns(T).Copy RngTo.Cells(C)
Next C
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Larry" wrote in message
...
I have the following code, I know there has to be some way to improve on
it.
I would greatly appreciate it if someone would give me some guidance on
it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 208
Default Code Improvements
Sub test2()
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")
wb1.Worksheets("txtMaster").Range("C1:C1500, F1:G1500, I1:K1500,
M1:M1500").Copy Workbooks("Book2").Sheets("Sheet1").Range("B3")
End Sub
--
Festina Lente


"Larry" wrote:

I have the following code, I know there has to be some way to improve on it.
I would greatly appreciate it if someone would give me some guidance on it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 159
Default Code Improvements
I would like to thank everyone with there response. You have given me a
great insight to further understand Excel and it's programming.
--
Larry E. Brueshaber


"Dana DeLouis" wrote:

Here's an additional idea, given your exact columns:

Sub Demo()
Dim RngFrom As Range
Dim RngTo As Range
Dim C As Long '(C)olumn
Dim T As Long '(T)emp

Set RngFrom = Workbooks("xlMasterFile.xls"). _
Worksheets("txtMaster"). _
Range("C1:M1500")

Set RngTo = Workbooks("Book2.xls"). _
Sheets("Sheet1"). _
Range("B3:H3")

For C = 1 To 7
'Adjuct Column number:
T = 155767382 Mod (C * 6 + 1)
RngFrom.Columns(T).Copy RngTo.Cells(C)
Next C
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Larry" wrote in message
...
I have the following code, I know there has to be some way to improve on
it.
I would greatly appreciate it if someone would give me some guidance on
it.
Thanks in advance and here is what code I have.

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range, rng4 As Range
Dim rng5 As Range, rng6 As Range, rng7 As Range, rng8 As Range
Dim rng9 As Range, rng10 As Range, rng11 As Range, rng12 As Range
Dim rng13 As Range, rng14 As Range
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFi le")


Set rng1 = wb1.Worksheets("txtMaster").Range("C1:C1500")
Set rng2 = wb1.Worksheets("txtMaster").Range("F1:F1500")
Set rng3 = wb1.Worksheets("txtMaster").Range("G1:G1500")
Set rng4 = wb1.Worksheets("txtMaster").Range("I1:I1500")
Set rng5 = wb1.Worksheets("txtMaster").Range("J1:J1500")
Set rng6 = wb1.Worksheets("txtMaster").Range("K1:K1500")
Set rng7 = wb1.Worksheets("txtMaster").Range("M1:M1500")

Set rng8 = Workbooks("Book2").Sheets("Sheet1").Range("B3")
Set rng9 = Workbooks("Book2").Sheets("Sheet1").Range("C3")
Set rng10 = Workbooks("Book2").Sheets("Sheet1").Range("D3")
Set rng11 = Workbooks("Book2").Sheets("Sheet1").Range("E3")
Set rng12 = Workbooks("Book2").Sheets("Sheet1").Range("F3")
Set rng13 = Workbooks("Book2").Sheets("Sheet1").Range("G3")
Set rng14 = Workbooks("Book2").Sheets("Sheet1").Range("H3")

rng1.Copy rng8
rng2.Copy rng9
rng3.Copy rng10
rng4.Copy rng11
rng5.Copy rng12
rng6.Copy rng13
rng7.Copy rng14
End Sub

--
Larry E. Brueshaber



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
Counting Consecutive Improvements SteveC Excel Discussion (Misc queries) 0 June 13th 06 01:35 AM
Performance Improvements while using RTD [email protected] Excel Programming 12 December 19th 05 12:10 PM
Message Box Improvements Phil Hageman[_4_] Excel Programming 3 June 17th 05 05:34 PM
ListView to Excel Code (but needs SPEED improvements) SVD Excel Programming 1 February 2nd 04 10:54 AM
anyone have any improvements for my Weeks Function? reesmacleod[_6_] Excel Programming 8 December 5th 03 11:30 PM


All times are GMT +1. The time now is 06:49 AM.

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