Code Improvements

[Larry]

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\xlMasterFile")


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

 

[Bob Umlas]

Sub test()
Dim wb2 as worksheet
Dim wb1 As Workbook

Set wb1 = Workbooks.Open("c:\Inventory\xlWorkFile\xlMasterFile")
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

 

[bplumhoff]

Hi Larry,

I suggest to "steal" some code:
http://www.erlandsendata.no/english/index.php?d=envbatextimportwb

HTH,
Bernd

 

[Gary Keramidas]

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\xlMasterFile")
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

 

[Dana DeLouis]

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

 

[PapaDos]

Sub test2()
Dim wb1 As Workbook

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

 

[Larry]

I would like to thank everyone with there response. You have given me a
great insight to further understand Excel and it's programming.