Copy and paste a row into column using macro

Question

I am very new to the macro..
Basically I wanted to copy a row (E23 to H23) from worksheet named "Present month" of one workbook to a column of another worksheet named "ANA" (K4 to K7) in another workbook.

Please help me out!!

Edit1: Code from comment

Sub Copy_and_update_last_col_Census()

Range("K4:K7").Select 
Selection.ClearContents 
Application.WindowState = xlNormal 
'the below line throws error 
Windows("NOL_Support_Ticket_Status_Report").Activate
Range("E25:H25").Select 
Selection.copy 
Windows("Charts.xlsm").Activate 
Range("K9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
Application.CutCopyMode = False
Selection.copy
Range("K4").Select 
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=True 
Application.CutCopyMode = False 
Range("K9:N9").Select 
Selection.ClearContents

End Sub

have you tried recording a macro? Developer Tab > Code > Record Macro. that's a good place to start. — L42, Feb 03 '14 at 06:07
Yes I tried but it is working fine when I do copy-paste within a same workbook. Is it possible across two different worbooks? — user3264654, Feb 03 '14 at 06:13
yes. copying and pasting between workbooks can be recorded. You'll see a `Windows` Object somewhere in the recorded code which will let you access other open workbooks. — L42, Feb 03 '14 at 06:16
np, actually the one who really helped you more than me is you yourself. :) I just lead you the way. if somehow you encounter errors or problems you cannot handle, come back here and all in SO are ready to give you a hand. :) goodluck! — L42, Feb 03 '14 at 07:19
SO I come back as you said :) actually when I am trying the run the same excelon another system (with same version of excel 2013) it is giving run time error — user3264654, Feb 05 '14 at 09:59
Sub Copy_and_update_last_col_Census() Range("K4:K7").Select Selection.ClearContents Application.WindowState = xlNormal 'the below line throws error Windows("NOL_Support_Ticket_Status_Report").Activate Range("E25:H25").Select Selection.copy Windows("Charts.xlsm").Activate Range("K9").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False — user3264654, Feb 05 '14 at 10:59
Selection.copy Range("K4").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Application.CutCopyMode = False Range("K9:N9").Select Selection.ClearContents End Sub — user3264654, Feb 05 '14 at 11:00
actually, this works fine, but if I change system it throws error at Windows("NOL_Support_Ticket_Status_Report").Activate — user3264654, Feb 05 '14 at 11:02
if that line throws error, make sure it is open and make sure you have the name right. When you are coding, it is best to avoid `.Select` and `.Activate`. See this [LINK](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) for ways how to avoid it. Btw, I'll post a code equivalent to yours without using `.Select` and `.Activate`. to give you a headstart. — L42, Feb 06 '14 at 00:40

score 0 · Answer 1 · answered Feb 03 '14 at 06:07

0

This is a tip. Go to the Developers tab -> Record Macro -> do the actions you want -> stop recording and woila press alt + F11 and check out the module.

F8 is your friend and you can see what your recorded macro does!!!

answered Feb 03 '14 at 06:07

SeekingAlpha

7,489
12
35
44

Is recording a macro possible across two different workbooks? I tried within same workbook/worksheet and it is working fine. – user3264654 Feb 03 '14 at 06:14

score 0 · Answer 2 · answered Feb 06 '14 at 01:49

try this:

Sub Copy_and_update_last_col_Census()

Dim wb As Workbook
Dim rng2copy As Range, rng2paste As Range
Dim query As Integer
Dim myfile
Dim filename As String

'~~> I assumed that Charts.xlsm is the workbook you are running the macro
Set rng2paste = ThisWorkbook.Sheets("ANA").Range("K4")
filename = "NOL_Support_Ticket_Status_Report.xlsx"
'~~> Handles the error if workbook is not open
On Error Resume Next
Set wb = Workbooks(filename)
'~~> check for error and execute action necessary
If Err.Number <> 0 Then
    query = MsgBox("Source workbook not open." & vbNewLine & _
            "Do you want to open it?", vbYesNo)
Else
    GoTo proceedcopy
End If

If query = 6 Then
    myfile = Application.GetOpenFilename(Filefilter:="Excel Files (*.xlsx), *.xlsx")
Else
    MsgBox "Exiting now."
    Exit Sub
End If
'~~> check if user selected the correct file
If myfile <> False Then
    If Dir(myfile) = filename Then
        Set wb = Workbooks.Open(myfile)
    Else
        MsgBox "Wrong file loaded." & vbNewLine & _
        "Exiting now."
        Exit Sub
    End If
Else
    MsgBox "No file loaded." & vbNewLine & _
        "Exiting now."
    Exit Sub
End If
'~~> this do the actual copying
proceedcopy:
Set rng2copy = wb.Sheets("Present Month").Range("E23", "H23")
rng2copy.Copy
rng2paste.PasteSpecial xlPasteValues, , , True
Application.CutCopyMode = False
wb.Close False

End Sub

This is tried and tested.
But i can't say that i've structured it well enough.
So i leave further testing to you.

I will try this code and disturb you again if I fail :) the Link you shared is very informative. Thank you again!! — user3264654, Feb 06 '14 at 09:52

Copy and paste a row into column using macro

2 Answers2