Prev: Computer that can solve Linear Programming problem?
Next: Copy a Textbox to a range on different worksheet
From: Amin on 20 Apr 2010 10:19
hi i try creating a macro to transfer data from one sheet to another but the
pasting is becoming tricky to me. i want to be able paste more than one row
or just one row depending on the data enterd in the transfer area. my macro
is using the relative ref and it paste the first 4 rows i selected correct
but when i used one row it not right

i will like to paste either more or less rows but it should always go to the
next available row thanks
From: Rick Rothstein on 20 Apr 2010 10:35
What is the "transfer area"? A little more detail about your setup, what you
want to copy and where would be helpful. Also, posting the code you have
that works for multiple rows but not for single rows would be useful to us
too.

--
Rick (MVP - Excel)



"Amin" <Amin(a)discussions.microsoft.com> wrote in message
news:B75EC334-37BC-4827-8A0F-155E60471949(a)microsoft.com...
> hi i try creating a macro to transfer data from one sheet to another but
> the
> pasting is becoming tricky to me. i want to be able paste more than one
> row
> or just one row depending on the data enterd in the transfer area. my
> macro
> is using the relative ref and it paste the first 4 rows i selected correct
> but when i used one row it not right
>
> i will like to paste either more or less rows but it should always go to
> the
> next available row thanks

From: Don Guillett on 20 Apr 2010 11:14
As ALWAYS, post YOUR code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Amin" <Amin(a)discussions.microsoft.com> wrote in message
news:B75EC334-37BC-4827-8A0F-155E60471949(a)microsoft.com...
> hi i try creating a macro to transfer data from one sheet to another but
> the
> pasting is becoming tricky to me. i want to be able paste more than one
> row
> or just one row depending on the data enterd in the transfer area. my
> macro
> is using the relative ref and it paste the first 4 rows i selected correct
> but when i used one row it not right
>
> i will like to paste either more or less rows but it should always go to
> the
> next available row thanks

From: Amin on 20 Apr 2010 12:29
hi thanks for the advice, here is the recording i made.

Sub Macro10()
'
' Macro10 Macro
'
' Keyboard Shortcut: Ctrl+t
'
Sheets("firstpage").Select
Range("A3:I9").Select
Selection.Copy
Sheets("Inventory").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("firstpage").Select
Range("Form[[PRODUCT ID]:[UNIT PRICES]]").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("Form[[CUSTOMER NAME]:[CONTACT NUMBER]]").Select
Selection.ClearContents
Range("A3").Select

End Sub

all i want to do is paste what ever data has been enterd into the designated
transfer rows whether is one row or i have more than one row it should always
use the next available empty row under the last row with data.

"Don Guillett" wrote:

> As ALWAYS, post YOUR code for comments
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "Amin" <Amin(a)discussions.microsoft.com> wrote in message
> news:B75EC334-37BC-4827-8A0F-155E60471949(a)microsoft.com...
> > hi i try creating a macro to transfer data from one sheet to another but
> > the
> > pasting is becoming tricky to me. i want to be able paste more than one
> > row
> > or just one row depending on the data enterd in the transfer area. my
> > macro
> > is using the relative ref and it paste the first 4 rows i selected correct
> > but when i used one row it not right
> >
> > i will like to paste either more or less rows but it should always go to
> > the
> > next available row thanks
>
> .
>
From: Don Guillett on 20 Apr 2010 13:25
Try this ONE liner to move values. I may have made a wrong assumption that
the cells tocopy are the same as those to delete????

sub cuttoodstsht()
Sheets("firstpage").Range("A3:I9").cut
Sheets("Inventory").Range("A2").end(xlDown).Offset(1)
end sub

If this doesn't work

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Amin" <Amin(a)discussions.microsoft.com> wrote in message
news:BD60FB29-F7E7-4315-88F3-1AF5EAACD347(a)microsoft.com...
> hi thanks for the advice, here is the recording i made.
>
> Sub Macro10()
> '
> ' Macro10 Macro
> '
> ' Keyboard Shortcut: Ctrl+t
> '
> Sheets("firstpage").Select
> Range("A3:I9").Select
> Selection.Copy
> Sheets("Inventory").Select
> Range("A2").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Sheets("firstpage").Select
> Range("Form[[PRODUCT ID]:[UNIT PRICES]]").Select
> Application.CutCopyMode = False
> Selection.ClearContents
> Range("Form[[CUSTOMER NAME]:[CONTACT NUMBER]]").Select
> Selection.ClearContents
> Range("A3").Select
>
> End Sub
>
> all i want to do is paste what ever data has been enterd into the
> designated
> transfer rows whether is one row or i have more than one row it should
> always
> use the next available empty row under the last row with data.
>
> "Don Guillett" wrote:
>
>> As ALWAYS, post YOUR code for comments
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "Amin" <Amin(a)discussions.microsoft.com> wrote in message
>> news:B75EC334-37BC-4827-8A0F-155E60471949(a)microsoft.com...
>> > hi i try creating a macro to transfer data from one sheet to another
>> > but
>> > the
>> > pasting is becoming tricky to me. i want to be able paste more than one
>> > row
>> > or just one row depending on the data enterd in the transfer area. my
>> > macro
>> > is using the relative ref and it paste the first 4 rows i selected
>> > correct
>> > but when i used one row it not right
>> >
>> > i will like to paste either more or less rows but it should always go
>> > to
>> > the
>> > next available row thanks
>>
>> .
>>

 |  Next  |  Last
Pages: 1 2
Prev: Computer that can solve Linear Programming problem?
Next: Copy a Textbox to a range on different worksheet