Help modifying an existing macro to remove all of the "fixed" ranges in it

[jenmwentworth]

I have taken over a project that someone previously developed the macros for. I would prefer it to work regardless of the dataset being used, but the person that developed it used mostly "fixed" ranges and I would prefer that it determines the range based on the data set.

The beginning of the macro retrieves the needed data from an external spreadsheet and pastes it to a "Data Import" spreadsheet. Then data is retrieved from the Data Import tab to "fill-in" specified cells on the monthly tabs.

When it retrieves the information it is pasting it to a specified range and I would rather it determine the paste range based on the last row of the table it's pasting to.

I am very new to macros and tried to make a couple changes, but caused it to error so obviously I missed something. Any help to make this more flexible would be appreciated.

Private Sub Loading_Jan_Click()
 
 'Dim the variables
 Dim FileSelect As Variant
 Dim wb As Workbook
 Dim i As Long
 'on error statement
 On Error GoTo errHandler:
 'hold in memory
 Application.ScreenUpdating = False
 'locate the file path
 FileSelect = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*", _
 MultiSelect:=False)
 'check if a file is selected
If FileSelect = False Then
 MsgBox "Select the file name"
 
 Exit Sub
 
 End If
 
 'clear the data
 Dim ws As Worksheet
 
 DataImport.Range("A1:T1000").ClearContents
 YTDTotals.Range("C3:c42").ClearContents
 
 'send the path to the worksheet
 Personal.Range("Y1").Value = FileSelect
   
 Dim Addme As Range, _
 CopyData As Range, _
 Bk As Range, _
 Sh As Range, _
 St As Range, _
 Fn As Range, _
 Tb As Range, _
 c As Range
 
 'on error statement
 On Error GoTo errHandler:
 
 'hold values in memory
 Application.ScreenUpdating = False
 
 'check neccessary cells have values
 For Each c In Personal.Range("Y2,Z2:AA2")
 If c.Value = "" Then
 MsgBox "You have left out a value that is needed in " & c.Address
 
 Exit Sub
 
 End If
 
 Next c
 
 'set the range reference variables
 Set Bk = Personal.Range("Y1") 'file path of book to import from
 Set Sh = Personal.Range("Y2") 'sheet to import
 Set St = Personal.Range("Z2") 'starting cell reference
 Set Fn = Personal.Range("AA2") 'finishing cell reference
 Set Tb = Personal.Range("AB2") 'sheet in this workbook to send it to
 
 'set the destination
 Set Addme = Worksheets(Tb.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
 Set wb = Workbooks.Open(Bk)
 
 'set the copy range
 Set CopyData = Worksheets(Sh.Value).Range(St & ":" & Fn)
 
 'copy and paste the data
 CopyData.Copy
 Addme.PasteSpecial xlPasteValues
 
 'clear the clipboard
 Application.CutCopyMode = False
  
  'close the workbook
 wb.Close False
  
 ' Sort and Load Export Sheet

January.Select
Dim z As Integer
Dim hsale As Range
Dim mmargin As Range
Dim ytdmargin As Range
Dim q As Integer
Dim x As Integer
Dim cc As Range
Dim cr As Integer
Dim ra As Integer
Dim y As Integer
Dim diff As Integer

 ' Sort and Load Monthly Sheet
' Store year to date totals for calculations

For y = 3 To 11

cr = 0
For z = 3 To 1000

If January.Range("A" & y) = DataImport.Range("C" & z) Then x = z

Next z

For q = 1 To 8

z = x + q
If DataImport.Range("D" & z) = "Totals" Then ra = z

Next q

If January.Range("A" & y) = 0 Then ytdmargin = 0
    
 Set hsale = DataImport.Range("H" & ra)    'Sales Amount
 Set mmargin = DataImport.Range("M" & ra)    'Margin Total Amount
 mmargin = mmargin * 0.01
 Set ytdmargin = DataImport.Range("T" & ra)    'YTD Margin Total Amount
 ytdmargin = ytdmargin * 0.01
 
January.Range("D" & y).Value = hsale
January.Range("E" & y).Value = mmargin
YTDTotals.Range("C" & y).Value = ytdmargin


test = 0
hsale = 0
mmargin = 0
xb = 0

Next y
  
  'return to the interface sheet
  Application.ScreenUpdating = True
  
 Exit Sub
 'error block
errHandler:
 MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
 & Err.Number & vbCrLf & Err.Description & vbCrLf & _
 "Please notify the administrator"
 End Sub

Commission Worksheet-Kitchen-sort testing recovered 3.xlsm
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Salesperson	Total January Net Sales $$ (Reported Sales + Sales Adjustments)	Sales Adjustments	Reported January Sales	Gross Margin %%	Net Margin %%	Commission $$'s (Total Sales * Rate)	Bonus markers (Total Sales Lookup)	Commission Bonus $$ (Total Sales Lookup)		Wk 1		Wk 2		Wk 3		Wk 4		Wk 5		OT ADJ.	Total Monthly Commission $$	Total YTD Sales $$
2											Total	OT	Total	OT	Total	OT	Total	OT	Total	OT
3	BH - Bridgette Henry	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
4	MM - Melanie Main	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
5	JEG - James Gillhooley	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
6	SLB - Seana Brown	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
7	CNR - Crystal Rubis	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
8	TRACI - Traci Ritchey	$0.00					$0.00	0	$0.00												$ -	$0.00	$0.00
9
January

Cell Formulas
Range	Formula
G3:G8	G3	=IFERROR(IF((B3>VLOOKUP(A3,Personal,4,FALSE)),(((B3-VLOOKUP(A3,Personal,4,FALSE))*VLOOKUP(A3,Personal,3,FALSE))+(VLOOKUP(A3,Personal,4,FALSE)*VLOOKUP(A3,Personal,2))),B3*VLOOKUP(A3,Personal,2,FALSE)),0)
H3:H8	H3	=VLOOKUP(B3,Bogey,3)
I3:I8	I3	=+H3*1250
U3:U8	U3	=IFERROR((((J3/K3)/2)*L3)+(((J3/M3)/2)*N3)+(((J3/O3)/2)*P3)+(((J3/Q3)/2)*R3)+(((J3/S3)/2)*T3),0)
V3:V8	V3	=G3+I3+U3
W3:W8	W3	=+B3
B3:B8	B3	=+D3+C3

Named Ranges
Name	Refers To	Cells
Bogey	=Bogey!$B$2:$G$28	H3:H8
January	=OFFSET(January!$A$3,0,0,COUNTA(January!$A:$A)-1,23)	G3

 

[mackc557]

Set CopyData = Worksheets(Sh.Value).Range(St & ":" & Fn)

This might cause an issue since St and Fn are ranges(specifically cells), but those cells are likely going to contain text that represents the cells to use. In the current situation it will likely use those as "Z2:AA2"
and not Value-At-Z2:Value-At-AA2. To fix this, change it to:

Set CopyData = Worksheets(Sh.Value).Range(St.value & ":" & Fn.value)