D
Dan R.
All I'm trying to do is copy Columns A, D, F, G, M, R, T to a new
spreadsheet (1st row to End(xlUp)).
This is how I'm doing it... it works fine, but can someone please show
me a better way?
Sub Test()
Dim Ws As Worksheet
Dim Wb As Workbook
Dim iEnd As Long
Dim iEnd2 As Long, iEnd3 As Long
Dim iEnd4 As Long, iEnd5 As Long
Dim iEnd6 As Long, iEnd7 As Long
Dim iRng As Range
Dim iRng2 As Range, iRng3 As Range
Dim iRng4 As Range, iRng5 As Range
Dim iRng6 As Range, iRng7 As Range
Set Ws = ActiveSheet
Set Wb = Workbooks.Open("C:\file.xls")
iEnd = Ws.Cells(Rows.Count, 1).End(xlUp).Row
Set iRng = Ws.Range(Ws.Cells(3, 1), Ws.Cells(iEnd, 1))
iEnd2 = Ws.Cells(Rows.Count, 4).End(xlUp).Row
Set iRng2 = Ws.Range(Ws.Cells(3, 4), Ws.Cells(iEnd, 4))
iEnd3 = Ws.Cells(Rows.Count, 6).End(xlUp).Row
Set iRng3 = Ws.Range(Ws.Cells(3, 6), Ws.Cells(iEnd, 6))
iEnd4 = Ws.Cells(Rows.Count, 7).End(xlUp).Row
Set iRng4 = Ws.Range(Ws.Cells(3, 7), Ws.Cells(iEnd, 7))
iEnd5 = Ws.Cells(Rows.Count, 13).End(xlUp).Row
Set iRng5 = Ws.Range(Ws.Cells(3, 13), Ws.Cells(iEnd, 13))
iEnd6 = Ws.Cells(Rows.Count, 18).End(xlUp).Row
Set iRng6 = Ws.Range(Ws.Cells(3, 18), Ws.Cells(iEnd, 18))
iEnd7 = Ws.Cells(Rows.Count, 20).End(xlUp).Row
Set iRng7 = Ws.Range(Ws.Cells(3, 20), Ws.Cells(iEnd, 20))
Set y = Wb.Sheets("Sheet1")
With Ws
iRng.Copy y.Cells(1, 1)
iRng2.Copy y.Cells(1, 2)
iRng3.Copy y.Cells(1, 3)
iRng4.Copy y.Cells(1, 4)
iRng5.Copy y.Cells(1, 5)
iRng6.Copy y.Cells(1, 6)
iRng7.Copy y.Cells(1, 7)
End With
End Sub
Thanks,
-- Dan
spreadsheet (1st row to End(xlUp)).
This is how I'm doing it... it works fine, but can someone please show
me a better way?
Sub Test()
Dim Ws As Worksheet
Dim Wb As Workbook
Dim iEnd As Long
Dim iEnd2 As Long, iEnd3 As Long
Dim iEnd4 As Long, iEnd5 As Long
Dim iEnd6 As Long, iEnd7 As Long
Dim iRng As Range
Dim iRng2 As Range, iRng3 As Range
Dim iRng4 As Range, iRng5 As Range
Dim iRng6 As Range, iRng7 As Range
Set Ws = ActiveSheet
Set Wb = Workbooks.Open("C:\file.xls")
iEnd = Ws.Cells(Rows.Count, 1).End(xlUp).Row
Set iRng = Ws.Range(Ws.Cells(3, 1), Ws.Cells(iEnd, 1))
iEnd2 = Ws.Cells(Rows.Count, 4).End(xlUp).Row
Set iRng2 = Ws.Range(Ws.Cells(3, 4), Ws.Cells(iEnd, 4))
iEnd3 = Ws.Cells(Rows.Count, 6).End(xlUp).Row
Set iRng3 = Ws.Range(Ws.Cells(3, 6), Ws.Cells(iEnd, 6))
iEnd4 = Ws.Cells(Rows.Count, 7).End(xlUp).Row
Set iRng4 = Ws.Range(Ws.Cells(3, 7), Ws.Cells(iEnd, 7))
iEnd5 = Ws.Cells(Rows.Count, 13).End(xlUp).Row
Set iRng5 = Ws.Range(Ws.Cells(3, 13), Ws.Cells(iEnd, 13))
iEnd6 = Ws.Cells(Rows.Count, 18).End(xlUp).Row
Set iRng6 = Ws.Range(Ws.Cells(3, 18), Ws.Cells(iEnd, 18))
iEnd7 = Ws.Cells(Rows.Count, 20).End(xlUp).Row
Set iRng7 = Ws.Range(Ws.Cells(3, 20), Ws.Cells(iEnd, 20))
Set y = Wb.Sheets("Sheet1")
With Ws
iRng.Copy y.Cells(1, 1)
iRng2.Copy y.Cells(1, 2)
iRng3.Copy y.Cells(1, 3)
iRng4.Copy y.Cells(1, 4)
iRng5.Copy y.Cells(1, 5)
iRng6.Copy y.Cells(1, 6)
iRng7.Copy y.Cells(1, 7)
End With
End Sub
Thanks,
-- Dan