Copy last row with data to next empty row...

[Oreg]

Hi,


I'm really stuck!!!! I've come across Ron de Bruin's website, which
provides some great solutions for copying to other workbooks. Here's
where I'm stuck. I would like to set my sourcerange to the last row
that holds data and copy that row to another workbook on the next empty
row of sheet1. I'm so close I can taste it !...I think When
I run the macro, I'm getting Here's what I've got so far.


Sub CopytoSP_history()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Dim Lr1 As Long

Application.ScreenUpdating = False
If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVENT
TRACKER\TrackerLog\METRO\SPhistory\SPHistory.xls")
End If
Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1,
0).Row
Lr1 = Sheets("METRO").Range("B" & Rows.Count).End(xlUp).Offset(0,
0).Row

Set sourceRange = ThisWorkbook.Worksheets("SP").Range("B" & Lr1)
Set destrange = destWB.Worksheets("Sheet1").Range("B" & Lr)

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Thanks,

Oreg

 

[Leith Ross]

Hello Oreg,

Working with multiple workbooks can be tricky. It is important t
remember to qualify your references fully to avoid getting tripped up


Let's look at your code here...
If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVEN
TRACKER\TrackerLog\METRO\SPhistory\SPHistory.xls")
End If
Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1
0).Row
Lr1 = Sheets("METRO").Range("B" & Rows.Count).End(xlUp).Offset(0
0).Row

The blue part refers to the destination workbook. The red to the sourc
workbook.
A problem occurs when the destination workbook is opened from you
code. The red statements now no longer reference the source worksheet
Without the source worksheet qualification, the system assumes th
worksheets are those of the active workbook, the destination.

REVISED CODE
With ThisWorkbook
Lr = .Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1
0).Row
Lr1 = .Sheets("METRO").Range("B" & Rows.Count).End(xlUp).Offset(0
0).Row
End Wit

 

[Oreg]

Hi Leith,

Thanks for the explanation on how the code works....trying to decipher
what a line of code is doing is mainly guesswork for me, so your
explanation helps out big time. Got a question for you. What I'm
attempting to do is the following:

I've created "EventTracker.xls" that auto-opens a form.
On the form is a button which, when pushed, will copy the last row of
data in Sheets("METRO"), open up a spreadsheet called "SPHistory.xls"
and paste that copied data into the next empy row of ("Sheet1").

So I was trying to point the current workbook with the following:
Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1,
0).Row

And trying to point to the active worksheet "METRO" with below:
With ThisWorkbook
Lr1 = Sheets("METRO").Range("B" & Rows.Count).End(xlUp).Offset(0,
0).Row
End With

But it's not working.


Thanks for your help and patience !!!!

 

[Dave Peterson]

Sometimes, it's easier to just set the range.

This compiled ok for me, but I didn't test it:

Option Explicit
Sub CopytoSP_history2()

Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook

Application.ScreenUpdating = False

If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVENT TRACKER\TrackerLog\" & _
"METRO\SPhistory\SPHistory.xls")
End If

With destWB
Set destRange _
= .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

With ThisWorkbook 'source workbook?
Set sourceRange _
= .Worksheets("SP").Cells(.Rows.Count, "B").End(xlUp).EntireRow
End With

sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False

Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

 

[Oreg]

Leith,

Thanks for helping me on this. After carefull review, I realized I
made a mistake in my original post & didn't copy Leith's code
correctly. Once I made those fixes, it worked perfectly.

Dave,

Your code did the trick as well. All I had to do was delete the period
before "Cells" and "Rows" and it went without a hitch.

Thanks guys, you're great !!!

Oreg

 

[Dave Peterson]

You shouldn't have had to delete those dots at all.

Those dots mean that the next object (rows) belongs to the previous With
statement (in this case one of the worksheets).

 

[Oreg]

Hi Dave,

I'm not sure why, but when I leave the dots, I keep getting the
following error:
"Method or data member not found"

As soon as I took the dots out, it worked fine. Any ideas why ? I'm
just curious because I'd like to get to the point....eventually...where
I actually know what I'm doing. Here's what the code looks like that
works error free for me.

Sub CopytoSP_history()

Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook

Application.ScreenUpdating = False

If bIsBookOpen("SPHistory.xls") Then
Set destWB = Workbooks("SPHistory.xls")
Else
Set destWB = Workbooks.Open("C:\EVENT TRACKER\TrackerLog\" & _
"METRO\SPhistory\SPHistory.xls")
End If

With destWB
Set destRange _
= cells(Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

With ThisWorkbook 'source workbook?
Set sourceRange _
= .Worksheets("SP").cells(Rows.Count, "B").End(xlUp).EntireRow
End With

sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False

Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


Thanks,

Oreg

 

[Dave Peterson]

This portion:

With destWB
Set destRange _
= cells(Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

is a problem.

When you have those unqualified ranges in a procedure in a general module, then
those ranges refer to the activesheet. If you're lucky, the correct sheet is
active. If you're not so lucky, then you won't get what you want. And the
"with destwb" isn't really doing anything.

But I had a problem in my code:

With destWB
Set destRange _
= .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

should have been:

With destWB.worksheets("whateverworksheetname")
Set destRange _
= .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).EntireRow
End With

And this portion:

With ThisWorkbook 'source workbook?
Set sourceRange _
= .Worksheets("SP").Cells(.Rows.Count, "B").End(xlUp).EntireRow
End With

should have been:

With ThisWorkbook.worksheets("SP")
Set sourceRange _
= .Cells(.Rows.Count, "B").End(xlUp).EntireRow
End With


Sorry about the errors.