Macro failed to copy and paste data range

**pjbassdc** · 09-02-2011, 04:09 PM

Greetings,

I have been trying to find a macro that can copy a range of data (A2:Q2) from a worksheet and paste it to the next available row (A:Q) of a worksheet in a different workbook. I am trying to get the 2nd workbook to be a running log of entries pulled from the 1st workbook. Anyway, I have had no luck trying to rework some of the macros I have seen due to various types of errors I have be unable to debug.

The code I am curently messing with acts like it is trying, but the desired data set doesn't make it to the 2nd workbook. I was hoping one of you all could give it a look and steer me down the right path. I know nothing of VBA and I have been looking for cookie-cutter solutions for my task.

Sub Copytolog()

Dim source As Range, dest As Range
Workbooks.Open FileName:="C:\Users\patrick\desktop\vandy stuff\working files\monthly log sheet.xlsx"

Set source = Workbooks("Tech form 4.0.xlsm").Worksheets("Pull sheet").Range("A2:Q2").End(xlUp)
Set dest = Workbooks("Monthly Log Sheet.xlsx").Worksheets("Sheet 1").Range("A2:Q2").End(xlUp)
source.Copy
dest.PasteSpecial
ActiveWorkbook.Close True
End Sub

Thank you for your time and assistance.

Patrick

**watersev** · 09-02-2011, 05:09 PM

hi, Patrick, in order to get data selection that is in A:Q range and number of rows might be different, you can use the following code line provided data is filled equally in all columns:

range("a1",cells(rows.count,"q").end(xlup))

So the first left upper cell is A1 and right bottom cell will be defined by going up till the first value is met.

This part of your code:

Range("A2:F2").End(xlUp)

do not select anything.

**pjbassdc** · 09-02-2011, 07:36 PM

Thanks for the reply...

I did more looking and messing around with some of the ideas I had found elsewhere. This is the solution that ended up working for me.

Sub CopyOneAreaValues()
Workbooks.Open FileName:="C:\Users\Patrick\Desktop\Vandy Stuff\Working Files\Monthly Log Sheet.xlsx"
    Dim sourceRange As Range
    Dim destrange As Range
    Dim Lr As Long
    Lr = LastRow(Workbooks("Monthly Log Sheet.xlsx").Sheets("Sheet 1")) + 1
        Set sourceRange = Workbooks("Tech form 4.0.xlsm").Worksheets("Pull sheet").Range("A2:Q2")
        With sourceRange
        Set destrange = Workbooks("Monthly Log Sheet.xlsx").Worksheets("Sheet 1").Range("A" & Lr). _
        Resize(.Rows.Count, .Columns.Count)
    End With
    destrange.Value = sourceRange.Value
    ActiveWorkbook.Close True
    End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Patrick