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
|