About Us

el dee

Thanks, That was the golden ticket!

"Bob Phillips" wrote:

Make your paste range a single column, not two, even if copying two columns.

For instance

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste

instead of

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

--
__________________________________
HTH

Bob

"el dee" wrote in message
...
Hi Barb,
I changed the line, I had it that way to begin with actually. I am still
recieving the same error. Any other ideas?
Thanks again

"Barb Reinhardt" wrote:

When you call the function, you've already defined wks to be the
worksheet
named "All Data Headers" in ThisWorkbook.

Try changing the offending line to this

Set SetPasteRangeByColumn = wks.Cells(lngRow, _
strColumn).End(xlUp).Offset(1, 0)

HTH,
Barb Reinhardt

"el dee" wrote:

Hi,
I know the 1004 error common and a problem, but I can't seem to get
around
this one. I have used this code before and it worked great. I have
denoted
the function on the bottom for debug....
Thanks!!

Const Maxrow = 50000
Sub Headers_To_Macro_Test()
' Headers_To_Macro_Test Macro
' Macro recorded 9/9/2009 by

Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data_Headers")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5:C5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A:B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B6:C6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C:D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5:F5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E:F")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H5:I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "K:L")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E6:F6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "G:H")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E7:F7")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "I:J")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "H6:I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "M:N")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "B9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "O")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "D9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "P")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "G9")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "Q")
rngCopy.Copy rngPaste

End Sub

Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
**Set SetPasteRangeByColumn =
Worksheets("All_Data_Headers").Cells(lngRow,
strColumn).End(xlUp).Offset(1, 0)--**Application defined or
object-defined
ERROR
End Function

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Export a chart in a GIF file. Run-time error '1004': Application-defined or object-defined error;	[email protected]	Excel Programming	4	September 16th 07 11:09 PM
Application-Defined or object defined error 1004 When ran on exel97 but not 2003	bornweb	Excel Programming	0	February 17th 07 11:30 PM
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined	Matt[_39_]	Excel Programming	3	July 25th 06 01:13 AM
Runtime error 1004- application defined or object defined erro	Novice	Excel Programming	1	February 6th 06 09:33 PM
Runtime error 1004- application defined or object defined erro	Jim Thomlinson[_5_]	Excel Programming	0	February 6th 06 09:29 PM

Menu

About Us