Solved paste to next blank cell in another ws

May 10, 2013 at 12:17:01
Specs: Windows 7
Hi there,
I have found various solutions to the above question but need to elaborate
I am entering date in to two cells , "Dashboard B6 and C6
I have created the following macro (AddSheetWithNameCheckIfExists) that applies the following,
Creates a new WS with the title of B6, lets call it 'Andy Pandy'
'Andy Pandy' then has 'freeze panes, and a command button generated, as required

Further to this, I also need to paste B6 and C6 to another sheet, lets call this 'Legend', to the next blank cell in a column , lets say column 'T'

Firstly what is the required instruction to paste to another sheet, column 'T', next blank cell,

and secondly, how do I integrate it with the following macro

<preSub AddSheetWithNameCheckIfExists()

Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets("DASHBOARD").Range("B6")
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
Range("D1").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Buttons.Add(30, 21, 72, 72).Select
Selection.OnAction = "hidescorecard"
Selection.Characters.Text = "Close ScoreCards"
With Selection.Characters(Start:=1, Length:=16).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.IncrementLeft -17.25
Selection.ShapeRange.IncrementTop 3#
ActiveWindow.SelectedSheets.Visible = False
Sheets("DASHBOARD").Select
End Sub>


Been scratching my head on this one
Looking for help


See More: paste to next blank cell in another ws

Report •


#1
May 10, 2013 at 12:44:42
Please click on the following line and read the instructions on how to post VBA code in this forum. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 10, 2013 at 12:57:07
Sub AddSheetWithNameCheckIfExists()

Dim ws As Worksheet
Dim newSheetName As String
newSheetName = Sheets("DASHBOARD").Range("B6")
For Each ws In Worksheets
If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If
Next
Sheets.Add Type:="Worksheet"
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = newSheetName
End With
 Range("D1").Select
 ActiveWindow.FreezePanes = True
 ActiveSheet.Buttons.Add(30, 21, 72, 72).Select
 Selection.OnAction = "hidescorecard"
 Selection.Characters.Text = "Close ScoreCards"
 With Selection.Characters(Start:=1, Length:=16).Font
 .Name = "Arial"
 .FontStyle = "Regular"
 .Size = 10
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ColorIndex = xlAutomatic
 End With
 Selection.ShapeRange.IncrementLeft -17.25
 Selection.ShapeRange.IncrementTop 3#
 ActiveWindow.SelectedSheets.Visible = False
 Sheets("DASHBOARD").Select
End Sub

OOps, sorry, long day working


Report •

#3
May 11, 2013 at 01:01:49
✔ Best Answer
You really should use indents in your code. VBA code is much easier to read if the code segments are delineated by indents.

The common method for finding the next empty cell in a column is as follows:

nxtRow = Sheets("Legend").Range("T" & Rows.Count).End(xlUp).Row + 1

You can then use the nxtRow variable as the Row number when referring to a Range:

Sub MacroName
 nxtRow = Sheets("Legend").Range("T" & Rows.Count).End(xlUp).Row + 1
  Sheets("DASHBOARD").Range("B6:C6").Copy _
    Destination:=Sheets("Legend").Range("T" & nxtRow)
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 11, 2013 at 08:23:43
Top man. I am still learning about VBA so thank you for you assistance
Look out for my next question, it won't be long in coming

Report •

Ask Question