Range as Active Cells Selection

[powellku]

Hey Excel Folks,

I'm having trouble with this one and I need some help.

My Objective: copy data from one excel sheet (rgSource) to a different destination sheet (rgDestination).

Problem: I can't get the Active cell selection to work and then have VBA automatically select the entire row.

Here's my code thus far:

Private Sub CommandButton4_Click()

'''' Move to Archive Button ''''

Dim rgSource As Range, rgDestination As Range
Set rgSource = ThisWorkbook.Worksheets("Database2").Range(Activecell.EntireRow) ''''Problem''''
Set rgDestination = ThisWorkbook.Worksheets("Archive").Range("A" & Application.Rows.Count).End(xlUp)

rgSource.Copy
rgDestination.PasteSpecial xlPasteValues

End Sub..

Thank you so much!

Sincerely,

kp

 

[Fluff]

If the button is on the DataBase2 sheet, then just use

 Set rgSource = Activecell.EntireRow

 

[powellku]

That works perfectly! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[powellku]

If the button is on the DataBase2 sheet, then just use

 Set rgSource = Activecell.EntireRow

One other question, now that section is working properly. I can't seem to get each row to add sequentially after another. I thought the code that I wrote "& Application.Rows.Count).End(xlUp)" would get VBA to add the next line of data sequentually, but its not doing that. Any suggestions?

 

[Joe4]

Range("A" & Application.Rows.Count).End(xlUp)

selects the last row with data, not the first blank one after that.

You need to do this:

Range("A" & Rows.Count).End(xlUp).Offset(1,0)

to select the first row after that.

 

[powellku]

@Fluff and @Joe4, both of you are awesome! I've been struggling with this all week and now it's working. I can't thank you enough!!!

 

[Joe4]

You are welcome.
Glad we could help!