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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.