copy data in specific columns to another sheet

[steve400243]

Hello, I'm looking for the best way to copy data from specific columns on the WK13 sheet, this will change weekly, to the DATA Tab. Here is what is needed.

from "WK Tab to Data Tab". Starting on Row 3 on the WK Tab. Please see the attached, and please let me know if I explained this good enough.

Column
D to A
E to B
C to C
G to D
B to E
AB to F
H to I

[werafa]

Hi,

i'd define worksheet and range objects, and then use these to bring the data in.

eg

dim mySheet as worksheet
dim rng1 as range, rng2 as range etc.

set mysheet = srcworkbook.worksheet
set rng1 = mysheet.range(the range)
set rng2 = mysheet.range(the range)

then you can paste the data using thisrange = thatrange,
or, if you arnt sure of the range dimensions

for myRow = 1 to rng1.rows.count
   this cell = thatcell
next myrow

you can pick the worksheet name a few different ways. my thought is to get a list of all worksheet names and get the user to select one from a validation list in a cell, and then trigger the routine from a command button

[steve400243]

Thanks for the tips werafa. I should be able to put this together.

[jolivanes]

Or you could try this.

Sub Transfer()
Dim fromArr, toArr, lr As Long, i As Long
Dim fromSht As Worksheet, toSht As Worksheet


fromArr = Array(4, 5, 3, 7, 2, 28, 8)
toArr = Array(1, 2, 3, 4, 5, 6, 9)


Set fromSht = Sheets("WK13")
Set toSht = Sheets("DATA")


lr = fromSht.Cells(Rows.Count, 4).End(xlUp).Row


    For i = LBound(toArr) To UBound(toArr)
        toSht.Range(toSht.Cells(2, toArr(i)), toSht.Cells(lr - 1, toArr(i))).Value = _
	fromSht.Range(fromSht.Cells(3, fromArr(i)), fromSht.Cells(lr, fromArr(i))).Value
    Next i


End Sub

[steve400243]

Or you could try this.

Sub Transfer()
Dim fromArr, toArr, lr As Long, i As Long
Dim fromSht As Worksheet, toSht As Worksheet


fromArr = Array(4, 5, 3, 7, 2, 28, 8)
toArr = Array(1, 2, 3, 4, 5, 6, 9)


Set fromSht = Sheets("WK13")
Set toSht = Sheets("DATA")


lr = fromSht.Cells(Rows.Count, 4).End(xlUp).Row


    For i = LBound(toArr) To UBound(toArr)
        toSht.Range(toSht.Cells(2, toArr(i)), toSht.Cells(lr - 1, toArr(i))).Value = _
    fromSht.Range(fromSht.Cells(3, fromArr(i)), fromSht.Cells(lr, fromArr(i))).Value
    Next i


End Sub

Works great, Thanks! I appreciate your time, and the code!

[jolivanes]

Glad to hear that you can use it.
Good luck