Copying entire Rows

[NewbieMan]

Hi,

My first post and thanks for the help in advance. Just learning VBA now and have a small question that I have been stuck on please.

Have the following...

Dim TeamID As Integer
Dim SearchRange As Range




AccessApp.DoCmd.OpenTable ("Coaches")
TeamID = InputBox(prompt:="Type the TeamID for your coach in the box below", Title:="Importing Coaches")


Set SearchRange = Columns("C:C")
SearchRange.Find(what:=TeamID).Select
ActiveCell.Offset(-1, 0).Select

Here is my problem...when I get to the TeamID cell and then up to the offset cell above it in column C, I then want to copy an array from cell A2 (fixed) down to and including the row containing the cell above TeamID cell and all the way over to the BC column (so that entire array) after accomplishing that I want to export into a specific table in access. What I am looking for is 1) how to copy that array , and then below it, 2) just a little nudge on how I would be able to paste that array into a specific table in access.

Thanks very much in advance. This is very much appreciated.

Cheers,

 

[NewbieMan]

please disregard the line that reads AccessApp.DoCmd.OpenTable("Coaches")

thanks

 

[mumps]

I'm not familiar with using Access so this macro is missing that part but it will copy the rows that you requested. You would have to add the pasting to Access part of it. I hope this helps.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim foundRow As Long
    Dim TeamID As Long
    TeamID = InputBox(prompt:="Type the TeamID for your coach in the box below", Title:="Importing Coaches")
    foundRow = Columns("C:C").Find(what:=TeamID).Row
    ActiveSheet.Rows("2:" & foundRow - 1).EntireRow.Copy
    'code to paste in Access
    Application.ScreenUpdating = False
End Sub

 

[NewbieMan]

I'm not familiar with using Access so this macro is missing that part but it will copy the rows that you requested. You would have to add the pasting to Access part of it. I hope this helps.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim foundRow As Long
    Dim TeamID As Long
    TeamID = InputBox(prompt:="Type the TeamID for your coach in the box below", Title:="Importing Coaches")
    foundRow = Columns("C:C").Find(what:=TeamID).Row
    ActiveSheet.Rows("2:" & foundRow - 1).EntireRow.Copy
    'code to paste in Access
    Application.ScreenUpdating = False
End Sub

Thanks mumps. One small issue otherwise it works fine. Just wondering how I can make an adjustment so that the entire row is copied only as far as Column "BC". As of right now it copies all columns.

Thanks.

 

[mumps]

Replace:

ActiveSheet.Rows("2:" & foundRow - 1).EntireRow.Copy

with

ActiveSheet.Range("A2:BC" & foundRow - 1).Copy

 

[NewbieMan]

thank you sir. worked like a charm

 

[mumps]

You are very welcome.