PDA

View Full Version : Solved: Adding extra vba code



BENSON
11-17-2007, 10:16 PM
The following code supplied by jimmy the hand works fine ,I would like to add the cell contents of "C285" and "C286" to the code so they appear on the same row as the pasted data from range "F274:AZ274"


Option Explicit
Sub CopyRange()
Dim WsTgt As Worksheet
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
ActiveSheet.Range("F274:AZ274").Copy
.Offset(, 1).PasteSpecial xlPasteValuesAndNumberFormats
End With
End Sub


Function NextEmptyRow(Wks As Worksheet) As Long
Dim Rng As Range
Set Rng = Wks.Range("A" & Wks.Rows.Count).End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
NextEmptyRow = Rng.Row
End Function



THANKS

malik641
11-17-2007, 10:36 PM
Hi,

You could do it explicitly:

Sub CopyRange()
Dim WsTgt As Excel.Worksheet
Dim rngCopy As Excel.Range

Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
Set rngCopy = ActiveSheet.Range("F274:AZ274")
rngCopy.Copy
.Offset(, 1).PasteSpecial xlPasteValuesAndNumberFormats

' Add C285 and C286
ActiveSheet.Range("C285").Copy
.Offset(, 2 + rngCopy.Columns.Count).PasteSpecial xlPasteValuesAndNumberFormats
ActiveSheet.Range("C286").Copy
.Offset(, 3 + rngCopy.Columns.Count).PasteSpecial xlPasteValuesAndNumberFormats
End With
End Sub
Untested, but it should work. Hope this helps.

EDIT: Change the code a little (in the .Offset() parts)

BENSON
11-18-2007, 12:05 AM
Works perfectly thanks very much for your quick response, just one thing hope you dont think I am pushing it would it be possible for cells "C285" and "C286" to appear at the begining of the row of pasted data

Many Thanks

malik641
11-18-2007, 12:50 AM
Yeah you could do that:

Sub CopyRange()
Dim WsTgt As Excel.Worksheet
Dim rngCopy As Excel.Range

Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
' Add C285 and C286
ActiveSheet.Range("C285").Copy
.Offset(, 1).PasteSpecial xlPasteValuesAndNumberFormats
ActiveSheet.Range("C286").Copy
.Offset(, 2).PasteSpecial xlPasteValuesAndNumberFormats

Set rngCopy = ActiveSheet.Range("F274:AZ274")
rngCopy.Copy
.Offset(, 3).PasteSpecial xlPasteValuesAndNumberFormats
End With
End Sub

Should work. Let me know :) (going to bed now, but will check back in the AM)

BENSON
11-18-2007, 05:44 AM
THANKS MALIK 641 works fine

BENSON
11-18-2007, 05:45 AM
THANKS MALIK 641 works fine