PDA

View Full Version : Solved: VBA CODE FOR PASTE VALUES



BENSON
11-15-2007, 09:36 PM
HI could some one help me with vba code to paste values from one workbook to another. I have a workbook called "Dailyreports GD WK 1" this workbook has seven sheets called TUES,WED etc thru to MON. I wish to paste the values in row F274 thru to AZ274 to a work book called "Gardens History". I would like to have the date of the pasting of the values recorded in collum A and the data to be pasted adjacent in collum b,c d,etc.I need the history range to be dynamic ie the values being pasted must be entered in the first availiable row

many thanks

JimmyTheHand
11-16-2007, 12:01 AM
Hello Benson,

Try this
Option Explicit

Sub CopyRange()
Dim WbSrc As Workbook, WsTgt As Worksheet
Dim i As Long, DayArr

DayArr = Array("TUES", "WED", "THUR", "FRI", "SAT", "SUN", "MON")
Set WbSrc = Workbooks("Dailyreports GD WK 1.xls")
Set WsTgt = Workbooks("Gardens History.xls").Sheets(1)

For i = LBound(DayArr) To UBound(DayArr)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
WbSrc.Sheets(DayArr(i)).Range("F274:AZ274").Copy .Offset(, 1)
End With
Next
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

Notes:
1.
You gave only 3 sheetnames, so they are known for sure, the rest are my best guess.
2.
The target sheet was not specified. I assumed the h?story records go to the 1st sheet of Garden History.xls
3.
In my experience, workbook references (e.g. Workbooks("Gardens History.xls") ) are case sensitive, which may lead to errors, if the workbook is called "Gardens History.XLS", for example.

Jimmy

BENSON
11-16-2007, 01:00 AM
Jimmy thanks for the quick reply , your code enters the date in collum A fine.However it is subtracting the contents of the two rows above and giving me the sum of the deduction. for example date shows in collum A126 "correct" B126 = B124-B125

JimmyTheHand
11-16-2007, 02:26 AM
Are there any formulas in range F274:AZ274 ?

BENSON
11-16-2007, 02:55 AM
Sorry jimmy , I should have realised yes there are F272- F273 .I really appreciate your help How do I alter the code

Thanks

JimmyTheHand
11-16-2007, 03:53 AM
Replace the loop part of above sub with this:

For i = LBound(DayArr) To UBound(DayArr)
With WsTgt.Range("A" & NextEmptyRow(WsTgt))
.Value = Date
WbSrc.Sheets(DayArr(i)).Range("F274:AZ274").Copy
.Offset(, 1).PasteSpecial xlPasteValuesAndNumberFormats
End With
Next

BENSON
11-16-2007, 10:46 PM
Jimmy thanks again for your help the code is nearly working correctly just a couple of things.At the moment it pastes only the values for TUES + WED at the same time even if I have say FRI as the active work sheet.I want it to only paste the data of the active work sheet The code also places an extra date in collum A below the just pasted data.This means that if I run the macro again it skips a row as it is looking for thenext completly blank row .The last thing is I get the following run time error 9 subscript out of range "WbSrc.Sheets(DayArr(i)).Range("F274:AZ274").Copy" I hope yoy can spare me some more of your time on this thanks alot Jimmy

JimmyTheHand
11-16-2007, 11:44 PM
At the moment it pastes only the values for TUES + WED at the same time even if I have say FRI as the active work sheet.I want it to only paste the data of the active work sheet The code also places an extra date in collum A below the just pasted data.This means that if I run the macro again it skips a row as it is looking for thenext completly blank row .The last thing is I get the following run time error 9 subscript out of range "WbSrc.Sheets(DayArr(i)).Range("F274:AZ274").Copy"
These are probably results of insufficient input. /EDIT: And my headlong rush into solving an imaginary problem before asking first :o:. EDIT/ You didn't mention that you only want to log the active sheet, and also didn't tell the names of the sheets.

The problem most likely lies with me giving the wrong name for thursday's worksheet.
DayArr = Array("TUES", "WED", "THUR", "FRI", "SAT", "SUN", "MON") The above line of code contains the list of sheet names. I suppose, your thusday's sheet is not called "THUR", which was my guess, but "THU" or something else, instead. That would explain he error message and, in general, the behaviour you described.

But this is all irrelevant now, because you want the code to work only on the active sheet. In my opinion, it is a brave thing to rely on the proper sheet being active when the code is run, but if you want it that way, here is how I would change the code:

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
Jimmy

BENSON
11-17-2007, 09:16 PM
THANKS JIMMY THE CODE WORKS GREAT