Copying ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello guys,

I have a worksheet with a small range meant for one user. The goal is to
copy that small range right besides the previous small range, with a changed
username, so each user has it's own small range to work in, all in the same
worksheet. Somehow this doesn't seem to work for me: the following error
keeps popping up: "method Range of object_Global failed". This is my code:

Sub bNaamInstellen_Klikken()
Dim UserName As String
Dim nextColumn As Range
UserName = InputBox(Prompt:="Gelieve de naam van de gebruiker te
typen:", _
Title:="Naam gebruiker wijzigen")
If UserName = "" Then
Exit Sub
Else
Range("bUserName") = UserName
End If
nextColumn = Range("bUserName", 1).End(xlToRight).Column + 1
Range("UserTotal").Copy Cells(nextColumn, 1)
End Sub

In this code i am first asking a username, set the range "bUserName" to the
given username, next i'm trying to copy the range "UserTotal" through the
nextColumn variabele contents to the first empty cell right of the UserTotal
range. I hope i am clear enough on this one... Otherwise feel free to reply
on this one, and i'll try to further clarify.

With regards,

Memento
 
I get the same error at this line:

Range("bUserName") = UserName

That's because I don't have the range bUserName already defined in my
workbook. Might that be your problem?

HTH,
Barb Reinhardt
 
No Barb,

This code works now with me, but offcourse you'll need to create the right
ranges in order to be able to duplicate this. The code that works now:

Sub bNaamInstellen_Klikken()
Dim UserName As String
Dim nextColumn As Single
UserName = InputBox(Prompt:="Gelieve de naam van de gebruiker te
typen:", _
Title:="Naam gebruiker wijzigen")
If UserName = "" Then
Exit Sub
Else
Range("bDefaultUserJPName") = UserName
End If
nextColumn = Cells(Rows.Count, 1).End(xlUp).Row + 1
Range("DefaultUser").Copy Cells(nextColumn, 1)
End Sub

But something strange is going on. Now i'm working with rows here with an
"xlUp" argument. the strange thing is that it's copying the DefaultUser range
UNDER the previous DefaultUser... Also, i am actually trying to get this to
work with columns, like this:

nextColumn = Cells(Columns.Count, 1).End(xlToRight).Column + 1

In other words: copy the "DefaultUser" range next to the current DefaultUser
 
Problem 1:
nextColumn = Cells(Rows.Count, 1).End(xlUp).Row + 1

If the last row in column 1 with an entry is 22, this will return row 23.
You call it nextColumn.

Problem 2:
nextColumn = Cells(Columns.Count, 1).End(xlToRight).Column + 1

I'd try this
nextColumn = Cells(columns.count,1).end(xltoleft).column + 1

HTH,
Barb Reinhardt
 
Oops, I take back what I just posted for Problem 2

Problem 2:

nextColumn = Cells(Columns.Count, 1).End(xlToRight).Column + 1

The Cells convention is Cells(Row, Column)

What I think you may want is
nextColumn = Cells(1,Columns.Count).End(xlToLeft).column + 1

This will find the last column in row 1 that has an entry.

HTH,
Barb Reinhardt
 
Back
Top