PDA

View Full Version : Solved: Move to end of data in Column in different worksheet



bdsii
07-21-2009, 07:27 PM
I have two files open. SampleData is open which has the data I want to move to another open file called Master. Master is the accumulation of data from imports like SampleData. I want to copy data from SampleData starting in A2 down to the end of the data with all four columns included. I do not need the header row copied. Column A will have data in each cell down the column but the other 3 columns of data may have empty cells.

What I would do manually is highlight A2 and do an end-down and then move to the right 3 more columns highlighting the area and Copy and then move the Master sheet go to the first blank row and paste the data into the Master.

I do not know the code to highlight down a column to the end of the data. I also do not know the code to move to the Master sheet and move down to the first blank row and then paste the copied data at that location.

I am only able to attach one file as a sample so I am attaching the SampleData file in case that helps.

I am using Excel 2007 in case that makes a difference.

Any help would really be appreciated! : pray2:

thanks!

Mavyak
07-21-2009, 07:50 PM
This code goes in the "ThisWorkbook" code module of the Master workbook:

Sub Get_Slave_Data()
On Error GoTo ErrHandler
Dim wbSourceWorkbook As Workbook
Dim wsSourceWorksheet As Worksheet
Dim rngSourceRange As Range
Dim fd As FileDialog
Dim x As Integer

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.AllowMultiSelect = True
.ButtonName = "Select"
.InitialFileName = "C:\" 'change this line to the directory where all the source data resides
.Title = "Select all the source files..."
.Show
If .SelectedItems.Count = 0 Then GoTo ExitSub

For x = 1 To .SelectedItems.Count
Set wbSourceWorkbook = Workbooks.Open(.SelectedItems(x))
Set wsSourceWorksheet = wbSourceWorkbook.Worksheets(1)
Set rngSourceRange = wsSourceWorksheet.Range("A2:C" & wsSourceWorksheet.Range("A65536").End(xlUp).Row)
rngSourceRange.Copy Destination:=ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0)
Set rngSourceRange = Nothing
Set wsSourceWorksheet = Nothing
wbSourceWorkbook.Close SaveChanges:=False
Set wbSourceWorkbook = Nothing
Next x
End With

ExitSub:
If Not rngSourceRange Is Nothing Then Set rngSourceRange = Nothing
If Not wsSourceWorksheet Is Nothing Then Set wsSourceWorksheet = Nothing
If Not wbSourceWorkbook Is Nothing Then
wbSourceWorkbook.Close SaveChanges:=False
Set wbSourceWorkbook = Nothing
End If
If Not fd Is Nothing Then Set fd = Nothing
Exit Sub

ErrHandler:
MsgBox "The following error has occured:" & vbCr & vbCr & Err.Description, vbOKOnly, "Error# " & Err.Number
Resume ExitSub
End Sub

bdsii
07-21-2009, 08:05 PM
Woo-Hoo! Thanks Mavyak!

A couple of things. I need it to copy to Column D so do I just change the statement below to ("A2:C".......to ("A2:D"..... ?

Set rngSourceRange = wsSourceWorksheet.Range("A2:C" & wsSourceWorksheet.Range("A65536").End(xlUp).Row)

Also, I do not need it to open the files for me or do multiple files at a time although now that you provided the code, that may be better than what I had envisioned to begin with. If I end up going with simply copying the data from an already opened sheet how would I code that ?

I noticed in your code that you put referenced "Range("A65536").End(xlUp).Row" a couple of times which calls out the end of the worksheet for a spreadsheet in Excel 2003. This would cover my example with that many rows included. However, is there a statement that specifies to copy to the end of the data or to go to the end of the existing data to paste ? For some reason I thought there was something like a End.Column or something else that would do it without specifying the lines. There may be a reason you used the row numbers and if so I would appreciate the explanation so I could learn more about this.

I appreciate the quick reply and the possible better solution than I had envisioned ! :thumb

bdsii
07-21-2009, 08:06 PM
Oh, well, it looks like my replacement of C with D in the post above actually inserted a smiley. I think you get what I mean though

Mavyak
07-21-2009, 08:24 PM
Yes, changing the "C" to a "D" will get the third column you need.

I missed that you were using Excel 2007. If there's a better way to get to the last used row in column A, feel free to use it. I haven't coded VBA for Excel in about a year and when I did it was with Excel 2003.

To capture a workbook that is already open you can iterate through the Workbooks collection (for each wb in Workbooks ... Next wb) and do a text comparison on the wb.Name property. Then set wbSourceWorkbook = wb.

bdsii
07-21-2009, 08:49 PM
Thanks Mavyak ! I appreciate it.

Possibly someone else can help shed light on an alternative method for moving to the end of data or highlighting to end of data other than specifiying rows. This may help others besides myself.

Anyone out there that can help ? :shouting:

GTO
07-21-2009, 08:58 PM
Greetings to all,

Try:

wsSourceWorksheet.Cells(Rows.Count, "A").End(xlUp).Row

Mark

bdsii
07-22-2009, 08:19 AM
I have found the following which will provide the count of Rows used.

ActiveSheet.UsedRange.Rows.Count

I can now create the following statement to establish a variable for this statement.

Dim lngLastRow As Long
With ActiveSheet
lngLastRow = ActiveSheet.UsedRange.Rows.Count
End With

What I hope to accomplish now is to use this variable called lngLastRow into the Range Select function.

If I were to manually select the cells it would look like this.
Range("A2:D5").Select

I have tried different variations on this statement to try to include the lngLastRow to replace the "5" in the statement above. I want A2 through D lngLastRow selected.

Does anyone know how I can include that in this simple statement ? Is it possible ?

thanks !

bdsii
07-22-2009, 09:43 AM
OK, I think I have this figured out with some help. I am posting this in case it helps anyone else out.

Sub SelectCells ()
' selects the cells to the end of data for copying elsewhere.
' this specific function below selects the cells from A2 down to the end
' of data and over to Column E and end of data
' Once this runs, you can then copy and paste or perform other functions

Range("A2:E" & ActiveSheet.UsedRange.Rows.Count).Select


' this is a Message Box that can be used to test to make sure the correct number of rows will be selected.

MsgBox ActiveSheet.UsedRange.Rows.Count, vbExclamation, "Number of Rows Used"

' this function below can be used to go to the next unused row in a spreadsheet
' if you run it after the section above to copy you can use this to move
' to the next empty row on the active spreadsheet and paste
' a variation of this could be used to copy data and then move to a
' different spreadsheet and move to the next empty row and paste there

Range("A" & (ActiveSheet.UsedRange.Rows.Count + 1)).Select

End Sub

This solves a lot of data handling issues for me and I hope that by sharing it that others can benefit as well. A lot of the experts on here probably already know this but for us newbies, we need all the help we can get :-)