Modifying VBA to create new macro for recording data

Hey,

I talked with you guys before about this project and you helped me with some macros which worked PERFECTLY, so thank you sooooo much for that. With the VBA code from the macros, I've been trying to edit the code to make the macro perform another action. Here is the code I'm editing in "Module 1": (This code takes AC3:AC21 from "Sheet1" and records the data in the "F4 - Max" worksheets, then deletes the contents in "Sheet1")

Sub TransferData2()

Dim RNG As Range, ws As Worksheet
Set RNG = Range("AC3:AC21")
Set ws = Sheets("" & Range("F4") & " - Max")
RNG.Copy ws.Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1)

Range("F4").Select
Selection.ClearContents
Range("K3:K5").Select
Selection.ClearContents
Range("M10:P29").Select
Selection.ClearContents
Range("K31:K33").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents

End Sub

And here is what I'm trying to create to get AD3:AD21 from "Sheet 1" to the "F4 - Avg" worksheet:

Sub TransferData3()

Dim RNG As Range, ws As Worksheet
Set RNG = Range("AD3:AD21")
Set ws = Sheets("" & Range("F4") & " - Avg")
RNG.Copy ws.Cells(3, Columns.Count).End(xlToLeft).Offset(0, 1)

Range("F4").Select
Selection.ClearContents
Range("K3:K5").Select
Selection.ClearContents
Range("M10:P29").Select
Selection.ClearContents
Range("K31:K33").Select
Selection.ClearContents
Range("F4").Select
Selection.ClearContents

End Sub

I'm not sure exactly why, but this macro isn't running when I push the "Record Data" button in "Sheet1"

Could somebody help me out here? And if it isn't too much to ask for, an explanation on why what I'm doing isn't working.

Thank you sooooo much for your time,
Bob

hi all,

Duplicate posting here: http://www.excelforum.com/excel-gene...t-working.html

Rob

hi Bob,

Thanks for modifying the thread title etc

I'm not sure at a glance why your code wasn't working but it may be because you haven't explicitly defined all your range references (eg "columns.count" instead of "ws.columns.count") & probably because your Counta check needed to be changed (see file). I've optimised your code in the attached file & it seems to work for me now.

hth
Rob