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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,761
Members
430,317
Latest member
karenmi

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
Top