CODEWITHSUNDEEP
vbaexcelcopy-paste
Alvaro Lopez E.
Alvaro Lopez E.

Reputation: 11

Excel - Copy-paste (transpose) between workbooks using VB

I'm trying to copy the values of a range of cells and then pasting it on a different workbook using VBA. However, the code only paste the first copied cell, leaving the rest of the range in blank. I couldn't identify my mistake.

This is the code:

Sub Data()
Dim wbk As Workbook`
Dim wks As Worksheet`
carp = ThisWorkbook.Path & "\Bases de datos\"
a = "Cuadros_de_salida_IMAE.xls"
Application.EnableEvents = False
Set wbk = Workbooks.Open(carp & a)
Set wks = wbk.Worksheets("Activ")
Application.EnableEvents = True
wks.Rows.Hidden = False


b = "D"
c0 = 9
c1 = 25
E = "IMAE"
f0 = "G"
f1 = "W"
g = 6
iniRow = c0
finRow = c1
wks.Range(b & iniRow & ":" & b & finRow).End(xlToRight).Copy
ThisWorkbook.Worksheets(E).Range(f0 & g).PasteSpecial  Paste:=xlPasteValues, Operation:=xlNone, Transpose:=True

Windows(a).Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

Thanks. I really appreciate some help.

Upvotes: 1

Views: 219

Answers (2)

Cyril
Cyril

Reputation: 6829

Took me a while to back-piece the code you have... seems like lots of unnecessary variables/references.

Dim LR as Long
LR = Cells(wks.Rows.Count, 1).End(xlUp).Row 

Dim LC as Long
LC = Cells(wks.Columns.Count, 1).End(xlToLeft).Column

wks.RANGE(Cells(9,7),Cells(LR,LC)).Copy

Worksheets(E).Range(Cells(6,7),Cells(LR-3,LC)).PasteSpecial  Paste:=xlPasteValues, Operation:=xlNone, Transpose:=True   'LR-3 because G9 and G6 row difference

That should be a decent start for you.

Upvotes: 1

J_Lard
J_Lard

Reputation: 1103

There's probably a better way to do this but the issue is with the wks.Range(b & iniRow & ":" & b & finRow).End(xlToRight).Copy statement. If you change this to:

wks.Range(b & iniRow & ":" & b & finRow).Select
Range(Selection, Selection.End(xlToRight).Copy

Your code should work. When in doubt use the macro recorder :)

Upvotes: 0

Related Questions