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
|