Copying Data

KenPopcorn

New Member
Joined
Nov 16, 2009
Messages
9
I swear this worked at one point but now gives a '1004' runtime error. What I am trying to accomplish is have the user display a line of data on a userform, modify it, then write it back to another sheet in the workbook.

My code:

Sub WriteData()
Dim target As Long
ActiveScreenUpdating = False
target = Range("B19").Value
'
If target > Sheets("WORK").Range("B3") Then
MsgBox ("Invalid Line Number")
GoTo ExitSub
End If
Sheets("GROUP 1").Range(target, 1) = Range("D19")
Sheets("GROUP 1").Range(target, 4) = Range("F19")
Sheets("GROUP 1").Range(target, 3) = Range("H19")
Sheets("GROUP 1").Range(target, 5) = Range("J19")
Sheets("GROUP 1").Range(target, 11) = Range("D22")
Range("B19").Activate
ExitSub:

End Sub

Please help this noob!

Thanks
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
What is the exact error message following the 1004 error line?
Application error?
Have you renamed a sheet so the sheet names in the macro are now incorrect perhaps?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
RANGE uses something like
Range("A1").

CELLS uses something like
Cells(row#,Column#)

so this
Sheets("GROUP 1").Range(target, 1) = Range("D19")
should be changed to
Sheets("GROUP 1").Cells(target, 1) = Range("D19")
or
Sheets("GROUP 1").Range("A" & target) = Range("D19")
 

KenPopcorn

New Member
Joined
Nov 16, 2009
Messages
9
The full error message is 'Application-defined or Object-defined error '1004'.

No changes to sheet names, and I have tried the code with both Cells and Range, and get the same error. :(
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

No changes to sheet names, and I have tried the code with both Cells and Range, and get the same error. :(

Post the code that fails using CELLS...

The other most likely problem is that target does not equal what you think it does...
Try specifying the sheet in this lline

target = Range("B19").Value
change to
target = Sheets("sheetname").Range("B19").Value


Also, try not to use target as your variable..that's a VBA built in function...try changing to MyTarget or something...
 
Last edited:

KenPopcorn

New Member
Joined
Nov 16, 2009
Messages
9
I believe it does not like the 'target' variable, I just don't know why.

Sub WriteData()
Dim target As Long
ActiveScreenUpdating = False
target = Range("B19").Value
'
If target > Sheets("WORK").Range("B3") Then
MsgBox ("Invalid Line Number")
GoTo ExitSub
End If
Sheets("GROUP 1").Cells(target, 1) = Range("D19") <<--- highlight is here
Sheets("GROUP 1").Cells(target, 4) = Range("F19")
Sheets("GROUP 1").Cells(target, 3) = Range("H19")
Sheets("GROUP 1").Cells(target, 5) = Range("J19")
Sheets("GROUP 1").Cells(target, 11) = Range("D22")
Range("B19").Activate
ExitSub:

End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

See the edit of my previous post...

make sure target actually = what you think it does.
Specify the sheet name on the target = Range... line
 

KenPopcorn

New Member
Joined
Nov 16, 2009
Messages
9
No change:

Sub WriteData()
Dim mytarget As Long
ActiveScreenUpdating = False
mytarget = Sheets("SUMMARY").Range("B19").Value
'
If mytarget > Sheets("WORK").Range("B3") Then
MsgBox ("Invalid Line Number")
GoTo ExitSub
End If
Sheets("GROUP 1").Cells(mytarget, 1) = Range("D19")<< -- Highlight is here
Sheets("GROUP 1").Cells(mytarget, 4) = Range("F19")
Sheets("GROUP 1").Cells(mytarget, 3) = Range("H19")
Sheets("GROUP 1").Cells(mytarget, 5) = Range("J19")
Sheets("GROUP 1").Cells(mytarget, 11) = Range("D22")
Range("B19").Activate
ExitSub:

End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It's gotta be that mytarget does not equal what you think it does..

Put in a message box

mytarget = Sheets("SUMMARY").Range("B19").Value
msgbox mytarget
 

KenPopcorn

New Member
Joined
Nov 16, 2009
Messages
9
It's official, I'm an idiot, but I was right, it did work at one time. Somewhere along the ling I eliminated ome extra lines on the userform, and neglected to change the "B19" to "B16".

Thanks, you tipped me off right where to look. Be well.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top