PDA

View Full Version : Solved: VBA Copy



08mcrisp
07-19-2011, 11:24 AM
Hi,

I need to make a macro that cuts range ("C1:E57") and pastes it to ("Sheet1") in the active cell and then moves the active cell one place to the right so that the next time the macro is run, the data that is in ("C1:E57") can be pasted next to the last set of data in ("Sheet1").

I am a complete beginner at macro but I had a go at it:

Sub NewYear()
'
' NewYear Macro
'
' Keyboard Shortcut: Ctrl+n
'

Range("C1:E57").Select
Selection.Copy
Sheets("Sheet1").Select
Offset(0, 1).Select.ActiveSheet.Paste

End Sub

This (of course) did not work but I have no idea what to try now!

Please help me!

Thanks and regards
08mcrisp

CatDaddy
07-19-2011, 11:30 AM
example workbook please?

08mcrisp
07-19-2011, 11:31 AM
Sorry!

What does that mean?

Apologies for my idiocy!

08mcrisp

CatDaddy
07-19-2011, 11:32 AM
hahaha no problem, can you attach a dummy of your workbook so i can see what you are trying to do?

08mcrisp
07-19-2011, 11:41 AM
Here it is.

I need to copy the cells that I specified earlier to sheet1 and then each time the macro is run, copy the new set of data in those cells next to the old set that is in sheet1.

Just thought if I rephrased my query it might make more sense.

Thanks for helping

Regards
08mcrisp

CatDaddy
07-19-2011, 12:39 PM
Sub moveColumns()
ActiveWorkbook.Sheets("Results").Activate
Range("A1").Activate
Dim cell As Range
Dim destCell As String
For Each cell In Range("A2:AZ2")
If cell.Value = "" Then
destCell = cell.Offset(-1, 0).Address
Exit For
End If
Next cell
ActiveWorkbook.Sheets("Sales").Activate
Range("A1").Activate
Range("C1:E57").Select
Selection.Copy
ActiveWorkbook.Sheets("Results").Activate
Range(destCell).Activate
ActiveSheet.Paste
End Sub


works just fine for me

08mcrisp
07-19-2011, 03:02 PM
Thank you! That worked amazingly! You are a legend!!!!!!! :bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow: :bow:

Brilliant!!
I can't thank you enough!!! :clap::clap::clap::clap::clap::clap::clap::clap::clap::clap::clap::clap:


08mcrisp

CatDaddy
07-19-2011, 03:07 PM
geez...how dare you make a black man blush? :D Just mark it as solved and pass it on!

08mcrisp
07-19-2011, 03:08 PM
How do I mark it as solved?

CatDaddy
07-19-2011, 03:12 PM
Top of the thread > Thread Tools > Marked Solved

08mcrisp
07-19-2011, 03:14 PM
I still can't find it!!

mancubus
07-20-2011, 01:21 AM
open with ie, if not already.