Macro to find last blank cell in column
Solved/Closed
LesE
Posts
2
Registration date
Friday June 12, 2009
Status
Member
Last seen
June 13, 2009
-
Jun 13, 2009 at 06:29 AM
LesE - Jun 13, 2009 at 05:56 PM
LesE - Jun 13, 2009 at 05:56 PM
Related:
- Find last empty cell in column vba
- Vba case like - Guide
- Number to words in excel formula without vba - Guide
- Excel vba find last non empty cell in column - Guide
- Vba check if value is in array - Guide
- How to open vba in excel mac - Guide
3 responses
OK Here it is.
I've added a few extra bits to suit my needs but I've left them in so as to be sure that what I have posted works.
The first routine selects a date, copies and pastes it to a cell in the sheet called Trade Calculator.
The next section copies a value from Data Input to the first empty cell in a column in Trade Calculator.
The next section does the same thing for another value to a different cell.
The next bit resets the date in the first routine back to today's date using the @NOW() function
The last bit confirms that everything worked ok.
Hope you find this useful,
Les
Sub HistoricalData()
Range("F6").Select
Selection.Copy
Sheets("Data Input").Select
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range
'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
Set SourceSht = ThisWorkbook.Sheets("Data Input")
'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
Set TargetSht = ThisWorkbook.Sheets("Trade calculator")
'This is the cells you will copy data from. This is targeting cells B1 to the last used cell in column B
Set SourceCells = SourceSht.Range("G6")
'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("J17").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
SourceCol = 1
ElseIf TargetSht.Range("J171").Value <> "" Then
'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
'Dont paste the data but advise the user.
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
'stop the macro at this point
Exit Sub
Else
'cell A1 does have data and we havent reached the last column yet so find the next available column
SourceCol = TargetSht.Range("J171").End(xlUp).Row + 1
End If
'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
SourceCells.Copy TargetSht.Cells(SourceCol, 10)
Range("F6").Select
Selection.Copy
Sheets("Data Input").Select
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
Set SourceSht = ThisWorkbook.Sheets("Data Input")
'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
Set TargetSht = ThisWorkbook.Sheets("Trade calculator")
Set SourceCells = SourceSht.Range("F6")
'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("E17").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
SourceCol = 1
ElseIf TargetSht.Range("E171").Value <> "" Then
'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
'Dont paste the data but advise the user.
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
'stop the macro at this point
Exit Sub
Else
'cell A1 does have data and we havent reached the last column yet so find the next available column
SourceCol = TargetSht.Range("E171").End(xlUp).Row + 1
End If
'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
SourceCells.Copy TargetSht.Cells(SourceCol, 5)
Range("F6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("F7").Select
'Advise the user that the process was successful
MsgBox "Data copied successfully!", vbInformation, "Process Complete"
Exit Sub 'This is to stop the procedure so we dont display the error message every time.
Err_Handler:
MsgBox "The following error occured:" & vbLf & "Error #: " & Err.Number & vbLf & "Description: " & Err.Description, _
vbCritical, "An Error Has Occured", Err.HelpFile, Err.HelpContext
End Sub
I've added a few extra bits to suit my needs but I've left them in so as to be sure that what I have posted works.
The first routine selects a date, copies and pastes it to a cell in the sheet called Trade Calculator.
The next section copies a value from Data Input to the first empty cell in a column in Trade Calculator.
The next section does the same thing for another value to a different cell.
The next bit resets the date in the first routine back to today's date using the @NOW() function
The last bit confirms that everything worked ok.
Hope you find this useful,
Les
Sub HistoricalData()
Range("F6").Select
Selection.Copy
Sheets("Data Input").Select
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim TargetSht As Worksheet, SourceSht As Worksheet, SourceCol As Integer, SourceCells As Range
'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
Set SourceSht = ThisWorkbook.Sheets("Data Input")
'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
Set TargetSht = ThisWorkbook.Sheets("Trade calculator")
'This is the cells you will copy data from. This is targeting cells B1 to the last used cell in column B
Set SourceCells = SourceSht.Range("G6")
'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("J17").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
SourceCol = 1
ElseIf TargetSht.Range("J171").Value <> "" Then
'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
'Dont paste the data but advise the user.
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
'stop the macro at this point
Exit Sub
Else
'cell A1 does have data and we havent reached the last column yet so find the next available column
SourceCol = TargetSht.Range("J171").End(xlUp).Row + 1
End If
'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
SourceCells.Copy TargetSht.Cells(SourceCol, 10)
Range("F6").Select
Selection.Copy
Sheets("Data Input").Select
Range("F6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'This is the sheet where your copy information from. Change "Sheet1" to the name of your soure sheet
Set SourceSht = ThisWorkbook.Sheets("Data Input")
'Name of the sheet where data is to be copied to. Rename Sheet2 to the name of your target sheet
Set TargetSht = ThisWorkbook.Sheets("Trade calculator")
Set SourceCells = SourceSht.Range("F6")
'This is finding the next column available in the target sheet. It assumes dates will be in row 1 and data in row 2 down
If TargetSht.Range("E17").Value = "" Then
'Cell A1 is blank so the column to put data in will be column #1 (ie A)
SourceCol = 1
ElseIf TargetSht.Range("E171").Value <> "" Then
'Cell IV1 has something in it so we have reached the maximum number of columns we can use in this sheet.
'Dont paste the data but advise the user.
MsgBox "There are no more columns available in the sheet " & TargetSht.Name, vbCritical, "No More Data Can Be Copied"
'stop the macro at this point
Exit Sub
Else
'cell A1 does have data and we havent reached the last column yet so find the next available column
SourceCol = TargetSht.Range("E171").End(xlUp).Row + 1
End If
'We can now start copying data. This will copy the cells in column B from the source sheet to row 2+ in the target sheet
SourceCells.Copy TargetSht.Cells(SourceCol, 5)
Range("F6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("F7").Select
'Advise the user that the process was successful
MsgBox "Data copied successfully!", vbInformation, "Process Complete"
Exit Sub 'This is to stop the procedure so we dont display the error message every time.
Err_Handler:
MsgBox "The following error occured:" & vbLf & "Error #: " & Err.Number & vbLf & "Description: " & Err.Description, _
vbCritical, "An Error Has Occured", Err.HelpFile, Err.HelpContext
End Sub
LesE
Posts
2
Registration date
Friday June 12, 2009
Status
Member
Last seen
June 13, 2009
7
Jun 13, 2009 at 02:12 PM
Jun 13, 2009 at 02:12 PM
Panic over! I've found what I want on another site.
aquarelle
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Jun 13, 2009 at 04:07 PM
Jun 13, 2009 at 04:07 PM
Hi
Super! but it would be nice to write the macro that you have found here because it could be useful to someone else.
Thank you in advance.
Super! but it would be nice to write the macro that you have found here because it could be useful to someone else.
Thank you in advance.
