VoG suggested this code to find the end of a sheet

[Glory]

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

For some reason this fails once I've copied data into the destination sheet. I would like to find the end of the used range of column "A".

I think I can just find the usedrange and then split the address using the colon or the $ as delimiters, but I'd rather use this cleaner method if I can to just hop to the bottom of the page and then back up to the bottom of my used range in a given column.

Any suggestions about this?

 

[jeffreybrown]

Not quite sure what your error is here.

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

This will find the last filled cell in column A and then go one down.

What are you doing, and what is failing?

 

[Glory]

I'm copying multiple excel files into a single sheet in another file.

The first one works fine.

The second onward has weird issues. I will explain more when I have the chance... any help you can offer in the meantime would be of great benefit.

 

[jeffreybrown]

any help you can offer in the meantime would be of great benefit.

Sure don't know what I can offer here without knowing what the weird issues are.

Also, don't know what macro you are using plus are you receiving some type of error?

Here is a great place to find some pre-built macros for doing what it sounds like you are doing.

Look under the Copy/Paste/Merge examples
http://www.rondebruin.nl/tips.htm

 

[Michael M]

If you're running the macro to find the last row and then changing the rows by adding removing data, you will have to rerun the macro or refind the "new" last row.

The find last row line of code does not "refresh" itself.

 

[Glory]

Sorry, not trying to be obscure, just trying not to overload you with unnecessary information.

Here's the code.

Sub test()
'running from book3
Set S1 = Workbooks("book1.xls").Sheets("Sheet1")
Set S2 = Workbooks("book2.xls").Sheets("Sheet2")
'===============================================
'Grabbing data from first file (book1.xls)
'===============================================
Workbooks.Open (ThisWorkbook.Path & "\book1.xls")
Windows("book1.xls").WindowState = xlMinimized
Set S3 = Workbooks("book1.xls").Sheets("sheet1")
Set S4 = Workbooks("book1.xls").Sheets("sheet2")
S1.Range("A2:J" & S1.Range("A" & Rows.Count).End(xlUp).Row).ClearContents
S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy S1.Range("A2")
S4.Range("A2:J" & S4.Range("A" & Rows.Count).End(xlUp).Row).Copy S2.Range("A2")
Workbooks("book1.xls").Close

'===============================================
'Grabbing data from second file (book1.xls)
'===============================================
Set S3 = Workbooks("book2.xls").Sheets("sheet1")
Set S4 = Workbooks("book2.xls").Sheets("sheet2")
Workbooks.Open (ThisWorkbook.Path & "\book2.xls")
Windows("book2.xls").WindowState = xlMinimized
S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
S4.Range("A2:J" & S4.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Workbooks("book2.xls").Close

Instead of just pasting into the destination sheet where the first file's data ends (which from what I understand is what the xlUp statement should be doing here given the sourcerange ["A" & Rows.Count]), the second file's data clears out parts of the sheet.

I see no explanation for this behavior. Any ideas?

 

[AlphaFrog]

[COLOR="Red"]Set S1 = Workbooks("book1.xls").Sheets("Sheet1")[/COLOR]
Set S2 = Workbooks("book2.xls").Sheets("Sheet2")
'===============================================
'Grabbing data from first file (book1.xls)
'===============================================
Workbooks.Open (ThisWorkbook.Path & "\book1.xls")
Windows("book1.xls").WindowState = xlMinimized
[COLOR="Red"]Set S3 = Workbooks("book1.xls").Sheets("sheet1")[/COLOR]

It appears that you've defined S1 and S3 as the same worksheet in the same workbook. Is that what you want?

 

[tusharm]

AFAIK, you cannot open a workbook with the same name as an already-open workbook. So, if the first 2 set statements work, you have an open workbook named book1.xls. So, workbooks.open ("...book1.xls") should fail.

What that means is that there's something strange going on with your code. I'd suggest stepping through it (with F8) to see what's actually happening.

Sorry, not trying to be obscure, just trying not to overload you with unnecessary information.

Here's the code.

Sub test()
'running from book3
Set S1 = Workbooks("book1.xls").Sheets("Sheet1")
Set S2 = Workbooks("book2.xls").Sheets("Sheet2")
'===============================================
'Grabbing data from first file (book1.xls)
'===============================================
Workbooks.Open (ThisWorkbook.Path & "\book1.xls")
Windows("book1.xls").WindowState = xlMinimized
Set S3 = Workbooks("book1.xls").Sheets("sheet1")
Set S4 = Workbooks("book1.xls").Sheets("sheet2")
S1.Range("A2:J" & S1.Range("A" & Rows.Count).End(xlUp).Row).ClearContents
S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy S1.Range("A2")
S4.Range("A2:J" & S4.Range("A" & Rows.Count).End(xlUp).Row).Copy S2.Range("A2")
Workbooks("book1.xls").Close

'===============================================
'Grabbing data from second file (book1.xls)
'===============================================
Set S3 = Workbooks("book2.xls").Sheets("sheet1")
Set S4 = Workbooks("book2.xls").Sheets("sheet2")
Workbooks.Open (ThisWorkbook.Path & "\book2.xls")
Windows("book2.xls").WindowState = xlMinimized
S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
S4.Range("A2:J" & S4.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Workbooks("book2.xls").Close

Instead of just pasting into the destination sheet where the first file's data ends (which from what I understand is what the xlUp statement should be doing here given the sourcerange ["A" & Rows.Count]), the second file's data clears out parts of the sheet.

I see no explanation for this behavior. Any ideas?

 

[Glory]

My bad... Those two variables are used to refer to two sheets in the same book. This is what I get for trying to keep proper nouns out of my posts.

I actually figured out what I was trying to figure out. The problem was that I was trying to preserve the field headings in my destination file, and xlUp was taking me from the bottom of the worksheet right into that single row of contents.

I needed to use offset(1,0) up there in that first block to make that problem go away.



Now I have a new problem:

Should these lines be giving me "Copy method of Range Class Failed"?

S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S1.Range("A" & Rows.Count).End(xlUp).Row

Eg, can the syntax Range("Whatever" & Rows.Count).End(xlUp).Row be used to set the destination for "Copy"?

 

[tusharm]

Post your actual code and don't try to "keep proper nouns out of my posts."

In a quick test I did, the following works as expected.

workbooks("book4").Sheets(1).cells(1,1).copy workbooks("book3").Sheets(1).cells(1,1)

My bad... Those two variables are used to refer to two sheets in the same book. This is what I get for trying to keep proper nouns out of my posts.

I actually figured out what I was trying to figure out. The problem was that I was trying to preserve the field headings in my destination file, and xlUp was taking me from the bottom of the worksheet right into that single row of contents.

I needed to use offset(1,0) up there in that first block to make that problem go away.



Now I have a new problem:

Should these lines be giving me "Copy method of Range Class Failed"?

S3.Range("A2:J" & S3.Range("A" & Rows.Count).End(xlUp).Row).Copy _
    S1.Range("A" & Rows.Count).End(xlUp).Row

Eg, can the syntax Range("Whatever" & Rows.Count).End(xlUp).Row be used to set the destination for "Copy"?