PDA

View Full Version : [SOLVED] copy data in specific columns to another sheet



steve400243
03-27-2017, 02:33 PM
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
03-27-2017, 02:43 PM
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
03-27-2017, 03:42 PM
Thanks for the tips werafa. I should be able to put this together.

jolivanes
03-28-2017, 07:33 PM
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
03-29-2017, 06:57 AM
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
04-29-2017, 10:31 PM
Glad to hear that you can use it.
Good luck