How to make macros flexible so that the worksheet and/or columns are not hardcoded into the macro?

[pgraft]

See below:

Sub CreatePivotTable()
'
' CreatePivotTable Macro
' select range before running macro macro will create a pivot table and add to data model
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    Workbooks("sheet1v2.xlsm").Connections.Add2 "WorksheetConnection_Sheet1!$A:$D" _
        , "", "WORKSHEET;C:\Users\pagr7\Desktop\[sheet1v2.xlsm]Sheet1", "Sheet1!$A:$D" _
        , 7, True, False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Sheet1!$A:$D"), Version:=6). _
        CreatePivotTable TableDestination:="Sheet3!R3C1", TableName:="PivotTable2" _
        , DefaultVersion:=6
    Sheets("Sheet3").Select
    Cells(3, 1).Select
End Sub

It's a pretty basic macro but end-user needs to be able to run it on various spreadsheets.

Also Is there a book that you would recommend on building macros?  I know my way around excel but have not played around with macros until recently.

thanks

[#2]

@pgraft The best way to change the sheet (so that it's not hard coded) is to declare the sheet as a variable. You can do the same with a column range.

An example might be:

Dim wbCurrentBook As Workbook
Dim wrkDataSheet As Worksheet
Dim wrkPivotSheet As Worksheet
Dim rngPivotRange As Range

Set wbCurrentBook = Application.ThisWorkbook
Set wrkDataSheet = wbCurrentBook.ActiveSheet
Set rngPivotRange = wrkDataSheet.Range("$A:$D")

Set wrkPivotSheet = wbCurrentBook.Sheets.Add

From there, you can use the variables in place of the hard coded values.