Macro to copy and paste range (2003 SP3)

[steveh]

Good afternoon

I have a workbook that will be opened usually 3 times per month and the data will refresh from the web, I would like to be able to click a button (which I already have (just a shape not a command button)) and save the data in the range to the next blank cell in column A opn another worksheet. I can get it to copy and paste but it overwrites the existing data. What do I need to do to make it find the next blank row, the 'remmed line in the code below was just my latest attempt.

Code: Select all

Sub SaveHistorical()

    Range("P2:AA38").Select
    Selection.Copy
    Sheets("Historical").Select
    Range("A3").Select
    'Set NextRow = Historical.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)[/color]    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DataSheet").Select
    Application.CutCopyMode = False
        Range("B3").Select
End Sub

[HansV]

Try this:

Code: Select all

Sub SaveHistorical()
    Range("P2:AA38").Copy
    With Sheets("Historical")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Note that the code doesn't activate the other sheet and doesn't change the current selection.

[steveh]

Try this:

Code: Select all

Sub SaveHistorical()
    Range("P2:AA38").Copy
    With Sheets("Historical")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Note that the code doesn't activate the other sheet and doesn't change the current selection.

Hi Hans

Thank you very much for that. It shows that the Macro Recorder is good but alos throws up some curved balls that can send you off on the wrong path

[HansV]

The macro recorder has inevitable limitations. It is very useful to get an idea of the kind of code you need for a task, but by its nature it cannot produce generalized code. So in many situations, you have to edit the code generated by the macro recorder.