problem with appending data to a spreadsheet 1

[atray04]

I am trying to copy parts of a spreadsheet from one workbook and appending it to the first column of the next free row. I looked for a append method and I could not find one so I was going to create a variable to hold what row I am on. Does this seem about right or is there a easier way? Right now my code just replaces the data on the spreadsheet. And I havent yet started to take cells of data and just copy that which I think will be easy just busy work.

 

[xlbo]

Pretty easy to find the last row. You are correct - there is no native "Append" statement in XL

Generally, I use this type of construct

set tWB = Thisworkbook
set oWB = activeworkbook 'it'll be active if you have just opened it
set AppSht = oWB.sheets("NewData&quot
set DataSht = tWB.sheets("AllData&quot
lRowA = AppSht.cells(65536,1).end(xlup).row
lRowD = DataSht.cells(65536,1).end(xlup).row
lCol = AppSht.cells(1,256).end(xltoleft).column
AppSht.range(cells(1,1),cells(lRowA,lCol)).copy destination:=DataSht.cells(lRowD+1,1)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[atray04]

what about this Open fName For Append As #1
I have not tested this line yet, but it was in the vba help files and this might append data if fName is c:\files\fname.xls
does this seem like it will do the same thing your code is doing?

 

[atray04]

Here is what ive donetWB is this workbook and sName(i) is the name of the worksheet)
Open fName For Append As #1
Set wb = ActiveWorkbook
wb.Sheets(1).Copy Destination:=tWB.Sheets(sName(i))
now it seems like this should work, but the error I am getting is application-defined or object-defined error. So I am stumped. Now the code you posted xlbo works fine, but I was getting some wrong information, because the InStr(fName, sName(i) did not find the correct string so I was seeing if this code might fix that. If not I could always change sName(3) to be a little more specific like instead of pc it could search for PC what do you think? I probably would also have to change the file names which would take a little bit.

 

[xlbo]

where did I post any code with
instr(fName , sName(i) ?????

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[atray04]

actually I did not mean to refer to that as you, but that was some code I was using that was giving me some problems with putting the correct data from different files into different spreadsheets. skipVought answered that post with this code:
Here's a way using instr...

Dim sName(3)
sName(0) = "file"
sName(1) = "Dinner"
sName(2) = "NC"
fname = "c:\file.xls"
For i = 0 To 2
If InStr(fname, sName(i)) > 0 Then
ws.Name = sName(i)
Exit For
End If
Next

 

[atray04]

ok, well nevermind that then. I was curious on your code and what some of the numbers mean. Could you please add some comments so I can understand that a little better?

 

[atray04]

OOps I forgot to say that the reason for this is because I am going to try to modify that code so instead of copying the whole range I can copy specific cells.

 

[xlbo]

ok - here goes:

'Set a reference to the workbook with the code
set tWB = Thisworkbook
'Set a ref to the workbook you want to get the data from
set oWB = workbooks.open("FullPath&Name.xls") 
'Set a ref to the sheet with data to append from
set AppSht = oWB.sheets("NewData")
'Set a ref to the sheet with data to append TO
set DataSht = tWB.sheets("AllData")
Get Last row of data on the sheet to import
lRowA = AppSht.cells(65536,1).end(xlup).row
'Get current last row of data in the current data set
lRowD = DataSht.cells(65536,1).end(xlup).row
'Find last column of the data to be appended
lCol = AppSht.cells(1,256).end(xltoleft).column
'Copy the data in the append sheet to the data in the data sheet
AppSht.range(cells(1,1),cells(lRowA,lCol)).copy destination:=DataSht.cells(lRowD+1,1)

Rgds, Geoff
[i][blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue][/i]
Want the [b][red]best[/red][/b] answers to your questions ? faq222-2244

 

[atray04]

yea, that helps thanxs