Input data at blank row at certain columns

[ChanL]

Hi, I want my VBA to run like this
1. copy data from sheet "fromtext" to the table in sheet "report" (everytime the data will copy to the last row of the table)
2. After copy the data, the table has two extra column "year" and "date" which require user manually input year and month of the copied data

my problem is at step 2, which I don't know what is the best and also the simplest way to automate manual year and month input

this is my code , please advice

With Sheets ("fromtext")
  . range("A9",.range("A9").end(xlDown).End(xltoright).copy
end with

With sheets("report").listobjects(1).range
   .cells(.rows.count+1,1).pastespecial xlvalues
end with

application.cutcopymode=false

 

[Joe4]

my problem is at step 2, which I don't know what is the best and also the simplest way to automate manual year and month input

Try using an InputBox to prompt the user for those values, i.e.

Dim yr as Long
yr = InputBox("Please enter the year")

You can then populate the cells that you want with that value.

If you need help with that, we will need more information, i.e. where these values are supposed to go.
Posting some images of what the data and tables look like would go a long way in helping us see that.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ChanL]

Hi, thanks so much for reply! I also think of input box but I google around still can't find a good way for me to write a code for it.
Here I include a mini sheet for my sample data.
So what I want is, after the VBA run to copy and paste the data to the table, the column for year and month will blank correspond to the new rows pasted. And here, if possible as you say, it would be great if VBA can prompt an input box for user to input year and month to it.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order ID	Ship Date	Order Date	Ship Mode	Customer ID	Customer Name	Destination	Contact Number	Status	Segment	Year	Month
2	CA-2014-105893	11/18/2014	11/11/2014	Standard Class	PK-19075	Pete Kriz	New York	123456	On the Way	Consumer
3	CA-2014-167164	5/15/2014	5/13/2014	Second Class	AG-10270	Alejandro Grove	Washington	123456	Failed	Consumer
4	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	New Zealand	123456	Delivered	Consumer
5	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	New York	123456	On the Way	Consumer
6	CA-2014-143336	9/1/2014	8/27/2014	Second Class	ZD-21925	Zuschuss Donatelli	Washington	123456	Delivered	Consumer
7	CA-2016-137330	12/13/2016	12/9/2016	Standard Class	KB-16585	Ken Black	Japan	123456	On the Way	Corporate
8	CA-2016-137330	12/13/2016	12/9/2016	Standard Class	KB-16585	Ken Black	New York	123456	Delivered	Corporate
9	US-2017-156909	7/18/2017	7/16/2017	Second Class	SF-20065	Sandra Flanagan	Washington	123456	On the Way	Consumer
10	CA-2015-106320	9/30/2015	9/25/2015	Standard Class	EB-13870	Emily Burns	New Zealand	123456	On the Way	Consumer
11	CA-2016-121755	1/20/2016	1/16/2016	Second Class	EH-13945	Eric Hoffmann	New York	123456	Failed	Consumer
12	CA-2016-121755	1/20/2016	1/16/2016	Second Class	EH-13945	Eric Hoffmann	Washington	123456	On the Way	Consumer
13	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	New Zealand	123456	Delivered	Consumer
14	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	New York	123456	On the Way	Consumer
15	US-2015-150630	9/21/2015	9/17/2015	Standard Class	TB-21520	Tracy Blumstein	Washington	123456	Delivered	Consumer
Sheet1

Try using an InputBox to prompt the user for those values, i.e.

Dim yr as Long
yr = InputBox("Please enter the year")

You can then populate the cells that you want with that value.

If you need help with that, we will need more information, i.e. where these values are supposed to go.
Posting some images of what the data and tables look like would go a long way in helping us see that.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Joe4]

Using the concepts I gave you in the last post, try something like this:

Sub InsertYearMonth()

    Dim yr As Long
    Dim mo As Long
   
    Dim fr As Long
    Dim lr As Long
   
'   Find first row needing data in column K
    fr = Sheets("report").Cells(Rows.Count, "K").End(xlUp).Row + 1
   
'   Find last row needing data (using column A)
    lr = Sheets("report").Cells(Rows.Count, "A").End(xlUp).Row
   
'   Prompt for year and month
    yr = InputBox("Please enter the year")
    mo = InputBox("Please enter the month")
   
'   Populate values into columns K and L
    Sheets("report").Activate
    Range(Cells(fr, "K"), Cells(lr, "K")) = yr
    Range(Cells(fr, "L"), Cells(lr, "L")) = mo

End Sub

 

[ChanL]

Using the concepts I gave you in the last post, try something like this:

Sub InsertYearMonth()

    Dim yr As Long
    Dim mo As Long
  
    Dim fr As Long
    Dim lr As Long
  
'   Find first row needing data in column K
    fr = Sheets("report").Cells(Rows.Count, "K").End(xlUp).Row + 1
  
'   Find last row needing data (using column A)
    lr = Sheets("report").Cells(Rows.Count, "A").End(xlUp).Row
 
'   Prompt for year and month
    yr = InputBox("Please enter the year")
    mo = InputBox("Please enter the month")
  
'   Populate values into columns K and L
    Sheets("report").Activate
    Range(Cells(fr, "K"), Cells(lr, "K")) = yr
    Range(Cells(fr, "L"), Cells(lr, "L")) = mo

End Sub

Hi thanks for the reply, i modify your code to fit in the WITH loop in my vba

dim nextrow as long, year as long, month as string, fr as long, lr as long
With Sheets("Sheet1").ListObjects(1).Range
  If .SpecialCells(xlConstants).Count = .Columns.Count Then
    nextrow = 2
  Else
    nextrow = .Rows.Count + 1
  End If
  .Cells(nextrow, 1).PasteSpecial xlValues

   fr = .Cells(Rows.Count, "K").End(xlUp).Row + 1
   lr = .Cells(Rows.Count, "A").End(xlUp).Row
 
year = inputbox("Enter the year")
month=inputbox("Enter the month")
.Range(Cells(fr, "K"), Cells(lr, "K")) = year
.Range(Cells(fr, "L"), Cells(lr, "L")) = month

and I hit application-defined error at

.Range(Cells(fr, "K"), Cells(lr, "K")) = year
.Range(Cells(fr, "L"), Cells(lr, "L")) = month

 

[Joe4]

Never use reserved words like "year" and "month" as the name of your variables!
Reserved words are names of existing functions, properties, methods, etc ("year" and "month" are names of existing Excel and VBA functions).
Using reserved words as the names of variables, procedures, or functions can cause errors and unexpected behavior.

That is why I used abbreviations in my example.
One thing I do if I am unsure if something is a reserved word or not is to prefix it with "my", i.e. "myMonth", "myYear", etc.

 

[ChanL]

Never use reserved words like "year" and "month" as the name of your variables!
Reserved words are names of existing functions, properties, methods, etc ("year" and "month" are names of existing Excel and VBA functions).
Using reserved words as the names of variables, procedures, or functions can cause errors and unexpected behavior.

That is why I used abbreviations in my example.
One thing I do if I am unsure if something is a reserved word or not is to prefix it with "my", i.e. "myMonth", "myYear", etc.

thanks for the advice. So, i changed the code

.Range(Cells(fr, "K"), Cells(lr, "K")) = year
.Range(Cells(fr, "L"), Cells(lr, "L")) = month

to

.Range(.Cells(fr, "K"), .Cells(lr, "K")) = yr
.Range(.Cells(fr, "L"), .Cells(lr, "L")) = mth

now the code doesn't hit the error, but the result is not as expected.
the code only insert the data from input box to the last row of column K and also the one of the row after it which does not contain data for column A to J.

 

[Joe4]

the code only insert the data from input box to the last row of column K and also the one of the row after it which does not contain data for column A to J.

Please post the following I can try to recreate your scenario:
1. The data set you are testing this on, before you run the code.
2. What the "report" sheet looks like, before you run the code.