Help with a macro again

bpflyr

Board Regular
Joined
Nov 7, 2005
Messages
116
I actually got an answer to this question a while ago, but kept getting a syntax error with the new macro, so I am posting the question again.

I have two workbooks, 'Data Entry' and 'Log Entry' (the one worksheet within them are the same names). The macro listed below will copy 4 rows of data from 'Data Entry' and paste it into 'Log Entry'.
(The macro is in 'Log Entry').

Can anyone please add to my macro so that each time I run it, the macro will look for the next blank row in 'Log Entry'! and do it's paste there? I am erasing all the data in 'Data Entry'! after each use.

Thanks in advance. Here's the macro:

Windows("BDataEntry.xls").Activate
Range("A4:AG8").Select
Selection.Copy
Windows("BLogbook.XLS").Activate
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RBartonSWW

New Member
Joined
Nov 18, 2005
Messages
38
I'm new to VBA...but picked things up quickly...learnt in a day at work as I had nothing else to do...but surely you want this line...

" SkipBlanks:=True _ "

to be set to False...as far as I would guess it's skipping all blanks and only pasting in the ones with stuff in.

I could be wrong...as I said I am new to this.
 

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
I am assuming that you want to paste the data in columns A:AG in BLogbook. The following will probably do what you want.

Range("A65536").End(xlUp).Offset(1, 0).Select
 

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
Sorry, I hit submit to quickly. This shows where the line should be inserted in your existing code.

Windows("BDataEntry.xls").Activate
Range("A4:AG8").Select
Selection.Copy
Windows("BLogbook.XLS").Activate
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, guys,
avoid to select
for example: Range("A4:AG8").Copy
it would be safer to tell the code which sheet to copy and to paste to

Code:
Windows("BDataEntry.xls").Activate
'you would better specify a sheet in next line
Range("A4:AG8").Copy
Windows("BLogbook.XLS").Activate
'you would better specify a sheet in next line
NR = Cells(Rows.Count).End(xlUp).Row + 1    'NR = next row :-)
Cells(NR, 1).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=True _
, Transpose:=False
End Sub

kind regards,
Erik

EDIT: "hit submit to early" you can edit your post or delete it (when not replied to)
using the "code"button let appear your code +- the way it is in the VBEditor
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,122
Members
412,305
Latest member
Mozz
Top