Copy,Paste,Transpose in VBA loop -Thank you so much!

[mytheresa]

Hello! I m new to macro and I need to copy the data from row 26:27, 36:37, 46:47 cells value which are converted into E9:F39, P9:Q39, AA9:AB39, 12 columns in the other workbook. How can I do if I want to import the data directly instead of making a copy of the report inside of my workbook by using VBA? Thanks a lot!

 

[Sajan]

Hello,

Can you clarify what you mean by "import data directly"?


If you just want to copy the data from one worksheet to another, you would not need a macro. (You can go to the target cell, type =, and then go to the source cell. Excel will add the reference to the source cell. You can then copy that target cell to additional columns or rows, and Excel will automatically adjust the referenced cells.)


You also mention that the cell values are "converted". Please describe what type of conversions are expected.


It would be best if you could post a sample layout of your source cells, and a sample output cells. (Or, you can consider posting a workbook. However, not everyone would be able to access posted files due to firewall restrictions.)

(Please see the post at the top of the forum home page for help with uploading files.)


Cheers,

Sajan.

 

[mytheresa]

my source file are in the row in one workbook like the picture

and I would like to use Marco to import the data from the row to the columns in another workbook, which looks like that and change everymonth

Because I need update the info every month, so I create several buttons to refresh data by using VBA. Thanks a lot if I clarify everything!

 

[SirJB7]

Hi, mytheresa!


Give a look at these files, first one is your source workbook, second is your target workbook:

https://dl.dropboxusercontent.com/u/60558749/Copy%2CPaste%2CTranspose%20in%20VBA%20loop%20-Thank%20you%20so%20much%21%20-%20Source%20%28for%20mytheresa%20at%20chandoo.org%29.xlsm

https://dl.dropboxusercontent.com/u/60558749/Copy%2CPaste%2CTranspose%20in%20VBA%20loop%20-Thank%20you%20so%20much%21%20-%20Target%20%28for%20mytheresa%20at%20chandoo.org%29.xlsm


In the second one there's a macro that does all the stuff with the condition that both files are opened. You can change the workbook and worksheets name to suit your needs, but take care that 3 ranges were created in both workbooks (Source1, Source2, Source3, for the 1st, and Target1, Target2, Target3, for the 2nd).


With that in mind, just run the following macro of target workbook whenever you want to retrieve the data from source workbook. As you didn't stated from which column of source worksheet you'd begin, I assumed column 1, just fyi if you need to adjust it).


Here's the macro code:

-----

[pre]

Option Explicit

Sub GetDataFromAnotherWorkbook()
'
' constants
'  source
Const ksWBSource = "Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm"
Const ksWSSource = "Hoja1"
Const ksRngSource = "X Source1 Source2 Source3"
'  target
Const ksWSTarget = "Hoja1"
Const ksRngTarget = "X Target1 Target2 Target3"
'
' declarations
Dim wbS As Workbook, wsS As Worksheet, rngS(3) As Range
Dim wbT As Workbook, wsT As Worksheet, rngT(3) As Range
Dim I As Long, J As Long, K As Integer, A() As String
'
' start
'  source
Set wbS = Workbooks(ksWBSource)
Set wsS = wbS.Worksheets(ksWSSource)
A = Split(ksRngSource)
For K = 1 To 3
With wsS
Set rngS(K) = .Range(A(K))
End With
Next K
'  target
Set wbT = ThisWorkbook
Set wsT = wbT.Worksheets(ksWSTarget)
A = Split(ksRngTarget)
For K = 1 To 3
With wsT
Set rngT(K) = .Range(A(K))
End With
Next K
'
' process
For K = 1 To 3
With rngT(K)
For I = 1 To .Rows.Count
For J = 1 To .Columns.Count
.Cells(I, J).Value = rngS(K).Cells(J, I).Value
Next J
Next I
End With
Next K
'
' end
'  source
For K = 1 To 3
Set rngS(K) = Nothing
Next K
Set wsS = Nothing
Set wbS = Nothing
'  target
For K = 1 To 3
Set rngT(K) = Nothing
Next K
Set wsT = Nothing
Set wbT = Nothing
'
End Sub

[/pre]
-----


Just advise if any issue.


Regards!

 

[SirJB7]

Hi, mytheresa!


Download again the updated "Target" file from same previous link. In worksheet Hoja2 you'll find a formula solution, with the consideration that INDIRECT function works only if related (source) workbook is open too.


The formulas for the 3 groups are these:

=INDIRECTO("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&DIRECCION(COLUMNA()+21;FILA()-8;4;1)) -----> in english: =INDIRECT("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&ADDRESS(COLUMN()+21,ROW()-8,4,1))

=INDIRECTO("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&DIRECCION(COLUMNA()+20;FILA()-8;4;1)) -----> in english: =INDIRECT("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&ADDRESS(COLUMN()+20,ROW()-8,4,1))

=INDIRECTO("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&DIRECCION(COLUMNA()+19;FILA()-8;4;1)) -----> in english: =INDIRECT("'[Copy,Paste,Transpose in VBA loop -Thank you so much! - Source (for mytheresa at chandoo.org).xlsm]Hoja1'!"&ADDRESS(COLUMN()+19,ROW()-8,4,1))


Regards!