Macro to ransfer data

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

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
 
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.
 
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.
 
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
(e-mail address removed)
Amin said:
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 said:
As ALWAYS, post YOUR code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)


.
Click to expand...
Click to expand...
 
One liner got broken up. Insert a line feed continuation _

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
Amin said:
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 said:
As ALWAYS, post YOUR code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top