Ignoring a blank cell while running a macro.

  • Thread starter Thread starter Decreenisi
  • Start date Start date
D

Decreenisi

Dear All

I have the following macro attached to a button to copy data F10:G40
(This months figures) to next free columns, so I can build up a yrs
worth of data. I then repopulate F10:G40 with the new months data,
then at the end of the month click the button and start over. The
problem is column "H" which is blank. I want the macro to start
pasting from column " I ". Is there a macro txt string that will ignor
a blank cell.

I don't want to nominate a particular cell, say =IF(C:C) because when
I transfer this to another sheet, the refs won't be the same. I just
need to generically skip any blanks.

Thanks in advance.

P.S I am new to macros so don't get all your terminology, sorry but it
needs to be idiot proof !!!

Range("F10:G40").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-30 (Question ? what does this mean &
can I take it out ?)
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("A1").Select

End Sub
 
Dear All

I have the following macro attached to a button to copy data F10:G40
(This months figures) to next free columns, so I can build up a yrs
worth of data. I then repopulate F10:G40 with the new months data,
then at the end of the month click the button and start over. The
problem is column "H" which is blank. I want the macro to start
pasting from column " I ". Is there a macro txt string that will ignor
a blank cell.

I don't want to nominate a particular cell, say =IF(C:C) because when
I transfer this to another sheet, the refs won't be the same. I just
need to generically skip any blanks.

Thanks in advance.

P.S I am new to macros so don't get all your terminology, sorry but it
needs to be idiot proof !!!

Range("F10:G40").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-30 (Question ? what does this mean &
can I take it out ?)
ActiveCell.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Range("A1").Select

End Sub

If I understand what you want, try just this. But wouldn't it be
better to copy below?
Sub copyrange()
Dim dlc As Long
dlc = Cells(10, Columns.Count).End(xlToLeft).Offset(, 2).Column
Range("F10:G40").Copy
With Cells(10, dlc)
.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteColumnWidths
End With
Application.CutCopyMode = False
Range("A1").Select
End Sub
 
Back
Top