Copy Paste Macro

magistercaesar

New Member
Joined
Jul 2, 2014
Messages
5
Hey guys! So I need a macro that will allow me to copy values from cells in one book to a table in another book.

I also need the macro to work repeatedly, ie.: It will copy the values to the next blank row in the table.

Basically, I need the following:

The value in K2 of book 1 needs to be copied to column C of book 2.
M3 of book 1 needs to be copied to column E of book 2.
L2 of book 1 needs to be copied to column F of book 2.
M5 of book 1 needs to be copied to column H of book 2.
L5 of book 1 needs to be copied to column I of book 2.

Thank you very much in advance!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Good thing I was bored today...

Code:
Function GetFileName() As Variant
Dim FInfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim FileName As Variant
'Set up list of file filters
FInfo = "All Files (*.*) ,*.*," & _
        "Old Excel (*.xls) , *.xls," & _
        "New Excel (*.xlsx) , *.xlsx," & _
        "Macro Excel (*.xlsm) , *.xlsm"
FilterIndex = 3            'Selecting 3rd item in list as default choice
Title = "Select a File containing the comment data"
'Get the filename
FileName = Application.GetOpenFilename(FInfo, FilterIndex, Title)
'Handle return info from dialog box
If FileName = False Then
    GetFileName = ""
Else
    GetFileName = FileName
End If
End Function

'lRow function is not necessary, but if constantly looking for last row,
'you may find helpful.
Function lRow(C As Long, Optional sh As Variant) As Long
On Error GoTo FixRow
If IsMissing(sh) Then Set sh = ActiveSheet
With sh
    lRow = .Cells(.Rows.Count, C).End(xlUp).Row + 1 'this line does all the work
    'the + 1 at the end finds the first blank row in given column
End With
Exit Function
FixRow:
Set sh = ActiveSheet
Resume
End Function


Sub CopyStuff()
Dim FileName As String
Dim wb As Workbook
Dim wsTarg, wsSrc As Worksheet
'**************************************************************************
'If you don't want to hard code the target workbook, use the following code
FileName = GetFileName()
If Len(FileName) > 1 Then
    Workbooks.Open FileName
    Set wb = Workbooks(Dir(FileName))
Else
    Exit Sub    'no workbook selected, exit sub
End If
'**************************************************************************
'now your workbook is opened, start copying
Application.ScreenUpdating = False
Set wsSrc = ThisWorkbook.Sheets("Sheet1")   'sheet you want to copy from
Set wsTarg = wb.Sheets("Sheet1")            'sheet copying to
With wsSrc
    .Range("K2").Copy wsTarg.Cells(lRow(3, wsTarg), "C")
    .Range("M3").Copy wsTarg.Cells(lRow(5, wsTarg), "E")
    .Range("L2").Copy wsTarg.Cells(lRow(6, wsTarg), "F")
    .Range("M5").Copy wsTarg.Cells(lRow(8, wsTarg), "H")
    .Range("L5").Copy wsTarg.Cells(lRow(9, wsTarg), "I")
End With
'wrapup
wb.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you so much! As an added thing, is it possible to change to code so that when it copies the cells, it pastes only the values? The macro works how I want it to work, but it is also copying the formats, which I forgot to take account of.
 
Upvote 0

Forum statistics

Threads
1,196,480
Messages
6,015,448
Members
441,895
Latest member
Zululander

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top