New to Macros: Creates in Sheet1 only

[Hiker]

Hi, I'm new to Macros and created one to take certain data from an existing workbook to be placed on a new sheet, which then renames it and moves it to its own workbook. All goes well until I run a similar Macro with different data. Apparently since I'm in the same session, this new sheet will be named Sheet2, an error is returned "subscript out of range". If I close Excel, and reopen I'll be able to run either Macro since Excel is back to Sheet1 again. I have very limited knowledge of VBA but could easily edit the code if it's only the matter of changing Sheet1 to SheetAny, or whatever. Thanks PS Macros were made using the recorder.

 

[Desu Nota from Columbus]

Hi, I'm new to Macros and created one to take certain data from an existing workbook to be placed on a new sheet, which then renames it and moves it to its own workbook. All goes well until I run a similar Macro with different data. Apparently since I'm in the same session, this new sheet will be named Sheet2, an error is returned "subscript out of range". If I close Excel, and reopen I'll be able to run either Macro since Excel is back to Sheet1 again. I have very limited knowledge of VBA but could easily edit the code if it's only the matter of changing Sheet1 to SheetAny, or whatever. Thanks PS Macros were made using the recorder.

Please post the code or relevant part of it.

You could try a with statement:

Sub test()

With ActiveSheet
    Range("A1").Value = 1
End With

End Sub

"With ActiveSheet" means that the following lines of code will be executed on the Sheet that is active (open) until the line "End With".

With the above code, whatever sheet is active when the macro is run, will have the number 1 placed in cell A1.

 

[Hiker]

Please post the code or relevant part of it.

    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 51
    Columns("AZ:AZ").Select
    Application.CutCopyMode = False
    Selection.Copy
   [COLOR=Red]Sheets("Sheet1").Select[/COLOR]
    Columns("B:B").Select
    ActiveSheet.Paste
    Sheets("Members").Select
    ActiveWindow.ScrollColumn = 50
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 48

Red font was the highlighted text from the debugger.

I don't want number 1 in A1.

As the macro is run now, it will only complete as intended if when it opens a new sheet to paste to, and that sheet needs to be labeled as sheet1 on the bottom tab. If I try to rerun the macro in the same session without saving, closing and reopening, the macro quits, I assume because it's no longer sheet1, but the new sheet automatically opened by the macro, is numbered sheet 2 or more.

I also assume, rather than scrolling from one column to another when copying and pasting data, there's a better way to get from one column to another. The goto function (F5)?