Having A lot of toruble with a Macro

[nathan663]

I have a spreadsheet that is basically a questionnaire. I have created a macro that takes certain answers from the questionnaire and puts them in a second spreadsheet but in a row.

However i need the macro to start again on the next available row. At the moment each time you run the macro it will just insert the data, over the cells already completed. (It will only put the data taken from the questionnaire on the row i set the macro up for.)

I need to add something to my macro in order to tell it to start on the next available row so i can use the macro over and over and keep all previous data.

Surely there must be a simple code to add to the beggining of the macro???

 

[cbzdmh]

You need to make the row number dynamic so it changes as the sheet updates. If you always have data Column A, this will put the data in the next available row.

finalrow = Cells(65536, 1).End(xlUp).Row
Answer = Cells(finalrow, 1).Value

 

[VoG]

You should have stuck to your original thread http://www.mrexcel.com/forum/showthread.php?t=379720

Please post the code that you currently have.

 

[schielrn]

It would help if we could see the code otherwise we have no idea what you are already doing?

 

[nathan663]

this is a draft of the code, only taking a few cells from the questionnaire, please note that there will be different marcos for different types of questionnaire (all still being put in row form on the second spreadsheet) So i need something i can add to each to make them start on the next available row

this is really driving me mad!

Sub Button40_Click()
'
' Button40_Click Macro
' Macro recorded 25/03/2009 by Nathan Gallichan
'
'
Range("L22").Select
Selection.Copy
Windows("Book4").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L23").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book4").Activate
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L24").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book4").Activate
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book4").Activate
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L26").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book4").Activate
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
Range("L27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book4").Activate
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[VoG]

As an example replace

Range("L22").Select
Selection.Copy
Windows("Book4").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

with

Range("L22").Copy
Windows("Book4").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

 

[Ragnar1211]

Your code is asking for trouble with this part:

Range("L22").Select
    Selection.Copy
Windows("Book4").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

and this part

Windows("New Accounts - KYC Checklists - Jersey new world 2009.xls").Activate
    Range("L23").Select
    Application.CutCopyMode = False
    Selection.Copy

A better idea would be to use workbook and worksheet names. Using select is not required either.

To help you further please post:

1) The worksheet you are using in: New Accounts - KYC Checklists - Jersey new world 2009.xls

2) The worksheet you are using in: Book4

 

[Ragnar1211]

Or just taking Vog's example a step further, replace all your code with:

Range("L22:L27").Copy
Windows("Book4").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False

[/quote]

But the code would be better off without the "Windows" part and the "Activate" part.