Home Menu

Menu


ExcelBanter » Excel Newsgroups » New Users to Excel > Macro to populate a list
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data to
enter more how can I transfer the second lot of results to the next row down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I need to
be.
Many thanks
Margs
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default Macro to populate a list
Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data to
enter more how can I transfer the second lot of results to the next row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I need
to
be.
Many thanks
Margs

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I have
copied the macro. I press Ctrl-d and all the information is transferred to
the other sheet but when I try to do it again with new information it over
writes the original row.
I am very grateful for your time.
Margs

TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Range("H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H2:K2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

"Per Jessen" wrote:

Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data to
enter more how can I transfer the second lot of results to the next row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I need
to
be.
Many thanks
Margs


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default Macro to populate a list
Hi Margs

Now that I have seen your macro, I will use .Offset(Row, Column) to change
row. See the code below:

Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Regards,
Per

"Margs" skrev i meddelelsen
...
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I have
copied the macro. I press Ctrl-d and all the information is transferred to
the other sheet but when I try to do it again with new information it over
writes the original row.
I am very grateful for your time.
Margs

TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Range("H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H2:K2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

"Per Jessen" wrote:

Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data
to
enter more how can I transfer the second lot of results to the next row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I
need
to
be.
Many thanks
Margs



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
Thanks Per, I brought up the edit screen for the macro, deleted what I had
entered then copied and pasted yours. When I tried to run the macro it came
up with an error message about a Compile Error and Syntax error. I've copied
the edit page below and it had highlighted in blue the line starting
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
and in red a similar line further down.
I really appreciate you helping me. I try to do things from books but it is
difficult to search for something when you don't know the name of what you
are searching for.
From a grateful Margs

Sub Clear()
'
' Clear Macro
' Clear Entry for next one
'
' Keyboard Shortcut: Ctrl+c
'
Range("H5").Select
Selection.ClearContents
Range("F9:O12").Select
Selection.ClearContents
End Sub
Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
False
End Sub



"Per Jessen" wrote:

Hi Margs

Now that I have seen your macro, I will use .Offset(Row, Column) to change
row. See the code below:

Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Regards,
Per

"Margs" skrev i meddelelsen
...
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I have
copied the macro. I press Ctrl-d and all the information is transferred to
the other sheet but when I try to do it again with new information it over
writes the original row.
I am very grateful for your time.
Margs

TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Range("H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H2:K2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

"Per Jessen" wrote:

Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of a
calculation to a row on another sheet. When I clear the original data
to
enter more how can I transfer the second lot of results to the next row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I
need
to
be.
Many thanks
Margs




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default Macro to populate a list
Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per


"Margs" skrev i meddelelsen
...
Thanks Per, I brought up the edit screen for the macro, deleted what I had
entered then copied and pasted yours. When I tried to run the macro it
came
up with an error message about a Compile Error and Syntax error. I've
copied
the edit page below and it had highlighted in blue the line starting
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
and in red a similar line further down.
I really appreciate you helping me. I try to do things from books but it
is
difficult to search for something when you don't know the name of what you
are searching for.
From a grateful Margs

Sub Clear()
'
' Clear Macro
' Clear Entry for next one
'
' Keyboard Shortcut: Ctrl+c
'
Range("H5").Select
Selection.ClearContents
Range("F9:O12").Select
Selection.ClearContents
End Sub
Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
False
End Sub



"Per Jessen" wrote:

Hi Margs

Now that I have seen your macro, I will use .Offset(Row, Column) to
change
row. See the code below:

Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Regards,
Per

"Margs" skrev i meddelelsen
...
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I
have
copied the macro. I press Ctrl-d and all the information is transferred
to
the other sheet but when I try to do it again with new information it
over
writes the original row.
I am very grateful for your time.
Margs

TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Range("H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H2:K2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

"Per Jessen" wrote:

Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a
more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of
a
calculation to a row on another sheet. When I clear the original
data
to
enter more how can I transfer the second lot of results to the next
row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I
need
to
be.
Many thanks
Margs





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per


"Margs" skrev i meddelelsen
...
Thanks Per, I brought up the edit screen for the macro, deleted what I had
entered then copied and pasted yours. When I tried to run the macro it
came
up with an error message about a Compile Error and Syntax error. I've
copied
the edit page below and it had highlighted in blue the line starting
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
and in red a similar line further down.
I really appreciate you helping me. I try to do things from books but it
is
difficult to search for something when you don't know the name of what you
are searching for.
From a grateful Margs

Sub Clear()
'
' Clear Macro
' Clear Entry for next one
'
' Keyboard Shortcut: Ctrl+c
'
Range("H5").Select
Selection.ClearContents
Range("F9:O12").Select
Selection.ClearContents
End Sub
Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
False
End Sub



"Per Jessen" wrote:

Hi Margs

Now that I have seen your macro, I will use .Offset(Row, Column) to
change
row. See the code below:

Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Regards,
Per

"Margs" skrev i meddelelsen
...
Hi Per,
I'm not sure what you want to see (sorry if I am being stupid) but I
have
copied the macro. I press Ctrl-d and all the information is transferred
to
the other sheet but when I try to do it again with new information it
over
writes the original row.
I am very grateful for your time.
Margs

TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Range("H3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("H2:K2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

"Per Jessen" wrote:

Hi Margs

To find the first empty row in column A you can use this:

EmptyCell=Range("A1").End(XlDown).Address

BTW: Showing the code you have made so far, will enable us to give a
more
precise answer.

Regards,
Per

"Margs" skrev i meddelelsen
...
I am new to macros but have worked out how to transfer the results of
a
calculation to a row on another sheet. When I clear the original
data
to
enter more how can I transfer the second lot of results to the next
row
down
on the second sheet.
I discovered a GoTo under Edit but cannot fathom if this is where I
need
to
be.
Many thanks
Margs






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default Macro to populate a list
Margs,

The macro is missing the paste statement for F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
Hi Pers,
Ive changed the Destination to F14:O14 but I get the Compile and Syntax
Error and the last Paste line is highlighted. I've copied the macro details
below.
Are you ready to throw in the towel !!
I wished I wasn't so stupid !!
Kind egards
Margs

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub




"Per Jessen" wrote:

Margs,

The macro is missing the paste statement for F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default Macro to populate a list
Hi Margs,
No need to throw the towel ;-)

My fault, I forgot about wordwrap, so the last past command should read:

Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

The only way to learn is to try.

Best regards,
Per

"Margs" skrev i meddelelsen
...
Hi Pers,
Ive changed the Destination to F14:O14 but I get the Compile and Syntax
Error and the last Paste line is highlighted. I've copied the macro
details
below.
Are you ready to throw in the towel !!
I wished I wasn't so stupid !!
Kind egards
Margs

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub




"Per Jessen" wrote:

Margs,

The macro is missing the paste statement for F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to
be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per




Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
Hi Per,
you know the saying about dumb blondes, well in my case it's true.
I copied and pasted that line in but the macro is showing other problems now
(it over writes the previous entry). So, what I am going to do is to start
with a new spreadsheet and go through all the points you've given me to see
if I can learn how the thing works. I'm okay with everything except the
formula for moving the destination cell down to the next blank row.
Would you mind if I got back to you when I get everything done just to help
me with that final point. Or, could you tell me what it is I am trying to do
so that I can look for in books.
Many thanks
Marg

"Per Jessen" wrote:

Hi Margs,
No need to throw the towel ;-)

My fault, I forgot about wordwrap, so the last past command should read:

Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

The only way to learn is to try.

Best regards,
Per

"Margs" skrev i meddelelsen
...
Hi Pers,
Ive changed the Destination to F14:O14 but I get the Compile and Syntax
Error and the last Paste line is highlighted. I've copied the macro
details
below.
Are you ready to throw in the towel !!
I wished I wasn't so stupid !!
Kind egards
Margs

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub




"Per Jessen" wrote:

Margs,

The macro is missing the paste statement for F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to
be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per





Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 703
Default Macro to populate a list
On 31 Maj, 22:05, Margs wrote:
Hi Per,
you know the saying about dumb blondes, well in my case it's true.
I copied and pasted that line in but the macro is showing other problems now
(it over writes the previous entry). So, what I am going to do is to start
with a new spreadsheet and go through all the points you've given me to see
if I can learn how the thing works. I'm okay with everything except the
formula for moving the destination cell down to the next blank row.
Would you *mind if I got back to you when I get everything done just to help
me with that final point. Or, could you tell me what it is I am trying to do
so that I can look for in books.
Many thanks
Marg



"Per Jessen" wrote:
Hi Margs,
No need to throw the towel ;-)

My fault, I forgot about wordwrap, so the last past command should read:

Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * xlNone, SkipBlanks:=False, Transpose:=False

The only way to learn is to try.

Best regards,
Per

"Margs" skrev i meddelelsen
...
Hi Pers,
Ive changed the Destination to F14:O14 but I get the Compile and Syntax
Error and the last Paste line is highlighted. I've copied the macro
details
below.
Are you ready to throw in the towel !!
I wished I wasn't so stupid !!
Kind egards
Margs

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
* *off = 0
Else
* *off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* *xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* *xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* *xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

"Per Jessen" wrote:

Margs,

The macro is missing the paste statement for *F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
* * off = 0
Else
* * off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted *this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to
be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
* *Sheets("Sheet1").Select
* * Range("H3").Copy
* * Sheets("Sheet2").Select

* * If Range("H2").Value = "" Then
* * * * off = 0
* * Else
* * * * off = Range("H1").End(xlDown).Row - 1
* * End If
* * Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * * * xlNone, SkipBlanks:=False, Transpose:=False
* * Sheets("Sheet1").Select
* * Range("H5").Copy
* * Sheets("Sheet2").Select
* * Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
* * * * xlNone, SkipBlanks:=False, Transpose:=False
* * Sheets("Sheet1").Select
* * Range("F14:O14").Select
* * Application.CutCopyMode = False
* * Selection.Copy
* * Sheets("Sheet2").Select
'False
End Sub

regards,
Per- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Marg

I haven't mentioned it, but I assume that you have a heading in H1
which is nessecary for the macro to work.

I have tested this macro, and it works fine here, but run the macro
more than 12 times the two paste areas will intersect.

Range("A1").Offset(1, 0 ) command is used to move 1 rows down and 0
columns right to A1. The variable "off" is used to tell how many rows
to offset.

You can always come back for more help.


Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select


If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If


Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Application.CutCopyMode = False


End Sub

Regards,
Per
Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 13
Default Macro to populate a list
Hi Per,
thank you so much for all your help. I'll end this thread but you may hear
from me again as I'll probably run into trouble. Look out for the dumb blonde!
Kind Regards
Margs

"Per Jessen" wrote:

On 31 Maj, 22:05, Margs wrote:
Hi Per,
you know the saying about dumb blondes, well in my case it's true.
I copied and pasted that line in but the macro is showing other problems now
(it over writes the previous entry). So, what I am going to do is to start
with a new spreadsheet and go through all the points you've given me to see
if I can learn how the thing works. I'm okay with everything except the
formula for moving the destination cell down to the next blank row.
Would you mind if I got back to you when I get everything done just to help
me with that final point. Or, could you tell me what it is I am trying to do
so that I can look for in books.
Many thanks
Marg



"Per Jessen" wrote:
Hi Margs,
No need to throw the towel ;-)

My fault, I forgot about wordwrap, so the last past command should read:

Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

The only way to learn is to try.

Best regards,
Per

"Margs" skrev i meddelelsen
...
Hi Pers,
Ive changed the Destination to F14:O14 but I get the Compile and Syntax
Error and the last Paste line is highlighted. I've copied the macro
details
below.
Are you ready to throw in the towel !!
I wished I wasn't so stupid !!
Kind egards
Margs

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

"Per Jessen" wrote:

Margs,

The macro is missing the paste statement for F14:O14, so the macro don't
know where to paste it. Add that line and it should work as desired.

Change DestinationCell to the cell to paste F14:O14 to.

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If

Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("DestinationCell").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

End Sub

Regards,
Per

"Margs" skrev i meddelelsen
...
I've copied and pasted this.
When I run the macro it copies and pastes H3 and H5 but only copies
F14..O14
but doesn't paste it.
Do you think I should look for an easier way of doing this as I seem to
be
putting you to a lot of trouble.
Regards
Margs

"Per Jessen" wrote:

Hi

The problem is wordwrap in your newsreader. Try this:

Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select

If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If
Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F14:O14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
'False
End Sub

regards,
Per- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Marg

I haven't mentioned it, but I assume that you have a heading in H1
which is nessecary for the macro to work.

I have tested this macro, and it works fine here, but run the macro
more than 12 times the two paste areas will intersect.

Range("A1").Offset(1, 0 ) command is used to move 1 rows down and 0
columns right to A1. The variable "off" is used to tell how many rows
to offset.

You can always come back for more help.


Sub TransferDetails()
'
' TransferDetails Macro
' Macro recorded 30/05/2008 by M.Liddell
'
' Keyboard Shortcut: Ctrl+d
'
Sheets("Sheet1").Select
Range("H3").Copy
Sheets("Sheet2").Select


If Range("H2").Value = "" Then
off = 0
Else
off = Range("H1").End(xlDown).Row - 1
End If


Range("H2:K2").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Select
Range("H5").Copy
Sheets("Sheet2").Select
Range("A5").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Sheets("Sheet1").Select
Range("F14:O14").Copy
Sheets("Sheet2").Select
Range("f14:o14").Offset(off, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Application.CutCopyMode = False


End Sub

Regards,
Per

Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 703
Default Macro to populate a list
Hi Margs,

Thanks for your reply. I'm glad we finally made it work!

You might want to follow the discussions in
"Microsoft.public.execl.programming" as you can learn many things from
reading the threads there.

Best regards,
Per

On 31 Maj, 23:31, Margs wrote:
Hi Per,
thank you so much for all your help. I'll end this thread but you may hear
from me again as I'll probably run into trouble. Look out for the dumb blonde!
Kind Regards
Margs

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a Validation List WLMPilot Excel Discussion (Misc queries) 3 December 5th 07 12:49 AM
How to populate part of a list dj479794 Excel Discussion (Misc queries) 2 July 2nd 07 10:10 AM
Populate List Box John in Surrey Excel Worksheet Functions 0 October 26th 05 11:25 AM
Populate List / Combo Box Dave C Excel Discussion (Misc queries) 1 October 6th 05 08:15 PM
Populate Drop Down List Wuttie Excel Worksheet Functions 1 May 2nd 05 08:21 PM


All times are GMT +1. The time now is 12:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement