PDA

View Full Version : Solved: Copy From One Sheet to Another



hobbiton73
04-04-2013, 08:28 AM
Hi, I wonder whether someone may be able to help me please.

I've put together the following code which extracts data from one sheet and copies it to another.

Sub AvailableDays()
Dim LR As Long, i As Long
With Sheets("Resource Summary")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("A" & i)
If .Value > 0 And .Value <> "Enter your name" Then .Copy Destination:=Sheets("Available Days").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Next i
End With
End Sub

However I've come up against a few hurdles, which I'm hoping someone may be able to help me with please.

Could someone perhaps tell me please how I may be able to adjust this script, so that the copy range starts from A5 and the paste range starts at A8?

I also have the additional problem, in that in it's current format the code pastes the figures and formula in the 'Destination' sheet.

Could someone possibly tell me please how I can change this so it pastes the data as 'Paste Special'?

Many thanks and kind regards

Chris

p45cal
04-04-2013, 09:35 AM
Assuming you mean PasteSpecial,Values, then:Sub AvailableDays()
Dim LR As Long, i As Long
With Sheets("Resource Summary")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LR
With .Range("A" & i)
If .Value > 0 And .Value <> "Enter your name" Then Sheets("Available Days").Cells(Application.Max(Sheets("Available Days").Cells(Rows.Count, "A").End(xlUp).Row + 1, 8), "A").Value = .Value
End With
Next i
End With
End Sub

hobbiton73
04-05-2013, 08:18 AM
Hi @p45cal, thank you once again, for taking the time help me out with this.

I've implented your solution and it works great.

All the best and kind regards

Chris