Macro to populate a list

  • Thread starter Thread starter Margs
  • Start date Start date
M

Margs

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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










- Vis tekst i anførselstegn -
Click to expand...

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
 
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
 
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
 
Back
Top