Solved: How to start pasting on the last row?

[genracela]

I have a VBA code:



Sub CopyTo()

Application.ScreenUpdating = True

Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all").Range("D2:D9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Range("A4").PasteSpecial Paste:=xlPasteValues

Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all").Range("E2:E9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Range("B4").PasteSpecial Paste:=xlPasteValues

Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all").Range("F2:F9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Range("C4").PasteSpecial Paste:=xlPasteValues
Application.ScreenUpdating = True

End Sub

Application.ScreenUpdating = True

End Sub


My problem is, I want to start pasting my data on the last row(or the blank row after the last data)

How can I put that in a code?

[Bob Phillips]

Sub CopyTo()
Application.ScreenUpdating = True

With Workbooks("MasterFile.xls").Sheets("Sheet2")

LastRow = .Cells(>Rows.Count, "A").End(xlUp).Row
End With

With Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all")

.Range("D2:D9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Cells(LastRow, "A").PasteSpecial Paste:=xlPasteValues
.Range("E2:E9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Cells(LastRow, "B").PasteSpecial Paste:=xlPasteValues
.Range("F2:F9000").Copy
Workbooks("MasterFile.xls").Sheets("Sheet2").Cells(LastRow, "C").PasteSpecial Paste:=xlPasteValues
End With

Application.ScreenUpdating = True
End Sub

[genracela]

Why is this one highlighted? and having a syntax error?


LastRow = .Cells(>Rows.Count, "A").End(xlUp).Row

The > character is highlighted

[GTO]

Twitchy little finger I would imagine... change to:

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

[genracela]

I tried deleting the > but it's highlighted in yellow

[Aussiebear]

Did you reset the debug?

[genracela]

I just did and it gives me "compile error: variable not defined"

and "LastRow" is highlighted in gray.

[Trebor76]

Hi genracela,

There's "Option Explicit" at the very top of the VBA module which means you must declare all variables. Replace the first four lines of your existing code with this:


Dim LastRow As Long

Application.ScreenUpdating = True

With Workbooks("MasterFile.xls").Sheets("Sheet2")

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

End With

HTH

Robert

[genracela]

Oh, ok...

Thanks a million! Mwahhhhh!!!

[rbrhodes]

My 2c with a nickels worth of comments


Option Explicit
Sub CopyTo()

'Declare all variables - always a good idea!
Dim lastrow As Long
Dim wbSrc As Worksheet
Dim wbDest As Worksheet
'Set to FALSE for speed/flicker
Application.ScreenUpdating = False

'Type in once!
Set wbDest = Workbooks("MasterFile.xls").Sheets("Sheet2")
Set wbSrc = Workbooks("MS Clearing File 040610.xls").Sheets("pbu006all")

'Get last row of data
lastrow = wbDest.Cells(Rows.Count, "A").End(xlUp).Row

'if lastrow > 1 then add 1 to equal first BLANK row
If lastrow > 1 Then
lastrow = lastrow + 1
End If

'Use created objects not looooooooooooong.......names
' also avoids typing names repeatedly: too many chances for 'tpyos'!
With wbSrc
.Range("D2:D9000").Copy
wbDest.Cells(lastrow, "A").PasteSpecial Paste:=xlPasteValues
.Range("E2:E9000").Copy
wbDest.Cells(lastrow, "B").PasteSpecial Paste:=xlPasteValues
.Range("F2:F9000").Copy
wbDest.Cells(lastrow, "C").PasteSpecial Paste:=xlPasteValues
End With

'Cleanup
Set wbSrc = Nothing
Set wbDest = Nothing

'Reset
Application.ScreenUpdating = True

End Sub

[genracela]

wow, that was sooooooo detailed :super:

I like it!

Thanks! Thanks! Thanks!:clap2: :mbounce2:

[rbrhodes]

Love the 'Smileys'

You're welcome!

[genracela]

please see continuation on:
http://www.vbaexpress.com/forum/showthread.php?t=32058


Follow up question...

Actually the code that you gave me was perfect. But, I got so excited that I want to add some modifications to my code. I hope you don't mind my follow-up question.

How about if FILENAME("filename.xls") of Source File(wbSrc) depends on the filename that is written in column A3 of destination file(wbDest)?

example:
if column A3 of wbDest is 12-May-10
then wbSrc filename is 12-May-10.xls
Now if I change 12-May-10 to 13-May-10 then my filename now is 13-May-10.xls


Thanks in advance!

[rbrhodes]

Love 'excited'. We're all learning here...


Look in this code for '// as marker of new & end new 'stuff'



Option Explicit
Sub CopyTo()

'Declare all variables - always a good idea!
Dim lastrow As Long
Dim wbSrc As Worksheet
Dim wbDest As Worksheet

'//NEW
Dim FileNameSrc As String
Dim FileSheetSrc As String 'JIC
'//End

'Set to FALSE for speed/flicker
Application.ScreenUpdating = False


'Type in once!
Set wbDest = Workbooks("MasterFile.xls").Sheets("Sheet2")

'//NEW
FileNameSrc = wbDest.Range("A3")

'Check for file ext. Code needs full name!
If Right(FileNameSrc, 4) <> ".xls" Then
FileNameSrc = FileNameSrc & ".xls"
End If

'This is JIC you wanted to specify sheet as well...

FileSheetSrc = "pbu006all" 'or range... eg: FileSheetSrc = wbdest.range("A4") or whatever...

'//Build it all here
Set wbSrc = Workbooks(FileNameSrc).Sheets(FileSheetSrc)

'//End

'//This will find at minimum row 4 as 'FileNameSrc' is now in A3. So 'If lastrow = 1' (below) is no longer needed...

'Get last row of data
lastrow = wbDest.Cells(Rows.Count, "A").End(xlUp).Row

'if lastrow > 1 then add 1 to equal first BLANK row
If lastrow > 1 Then
lastrow = lastrow + 1
End If

'Use created objects not looooooooooooong.......names
' also avoids typing names repeatedly: too many chances for 'tpyos'!
With wbSrc
.Range("D2:D9000").Copy
wbDest.Cells(lastrow, "A").PasteSpecial Paste:=xlPasteValues
.Range("E2:E9000").Copy
wbDest.Cells(lastrow, "B").PasteSpecial Paste:=xlPasteValues
.Range("F2:F9000").Copy
wbDest.Cells(lastrow, "C").PasteSpecial Paste:=xlPasteValues
End With

'Cleanup
Set wbSrc = Nothing
Set wbDest = Nothing

'Reset
Application.ScreenUpdating = True

End Sub

[genracela]

sorry again:(

and a lot of thank you (if that might help)