last cell

Asche

Board Regular
Joined
Jun 25, 2008
Messages
50
I know vb, well the basics.
I'm trying to make a button, that will open a .txt document, copy the numbers out of it, the paste them into the first cells that are open.
if some one could help that would be nice.
I've been messing around with some stuff, and gotten a little close kinda. but no where near where I need to be.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
This will return the first unused row in column A.

Code:
Dim NextRow As Long
NextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
 
Upvote 0
gave me an error. mite be the excel I am useing.
useing win2000.
and also that code starts from the last cell in the workbook. not from the top.


Can you post the code as you have it? What was the error?

It's supposed to start from the bottom, it then goes up to the last used row and returns that row+1 to give you the next available row, although you may want to add a check for row 1 availability:

Code:
Sub test()
Dim x As Long
x = Range("A" & Rows.Count).End(xlUp).Row
If x = 1 And Range("A" & x) = "" Then x = 0
x = x + 1
End Sub
 
Upvote 0
here is my code before putting in what you have

Option Explicit

Sub MacroCopyTputReport()
Dim NextRow As Long
Dim sNextCell As String, astatfile As String, sDestFile As String, sDestTab As String

Dim NextCell As Range

Dim DestFile As Workbook, SourceFile As Workbook
Dim DestTab As Worksheet, SourceTab As Worksheet


Set DestFile = ActiveWorkbook
Set DestTab = DestFile.ActiveSheet
'Set DestTab = DestFile.Sheets(DestFile)

astatfile = Application.GetOpenFilename

Workbooks.OpenText Filename:=astatfile, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1)) '' open txt file with excel 2000

Range("A1").Select
Set SourceFile = ActiveWorkbook
Set SourceTab = ActiveSheet
Range(Selection, Selection.End(xlDown)).Select '' cntl+shift down
Selection.Copy ' copy source data

' go back to excel sheet to paste
DestFile.Activate '' go back to xcel sheet to paste
DestTab.Select '' select destination sheet




' loop to find 1st blank cell in column A
'If Empty Then
'Range("A1").Select ' vai file
'ActiveCell.Paste
'Else
'NextRow = Range("A" & Rows.Count).Home(xlDown).Row + 1
'sNextCell = "A" & Rows.Count
'sNextCell = "A31"

'Range("A31").Select ' this is where the macro will paste the report values.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False '' paste values only

'End If
SourceFile.Close False '' Close the book containing the text file.

End Sub
 
Upvote 0
your code stuck it in d. I need it to stay in the 'a' column, not be jumping to d or c, like it keeps doing.
 
Upvote 0
My code doesn't put anything anywhere, it just puts the next avaiable row in variable x. It's also not necessary to do all that selecting.

Code:
x = Range("A" & Rows.Count).End(xlUp).Row
If x = 1 And Range("A" & x) = "" Then x = 0
x = x + 1
Range("A" & x).PasteSpecial xlPasteValues
 
Upvote 0
any way I found random code on the internet some where that made it work.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top