Macro to past based on columns

[karldugan]

Hi all,

Tried looking around for this one but can't find something that works.

I have data in columns A - D that a macro pre-populates, only column B has data in every cell. The macro copies a date from one sheet but what I want it to do is find the first empty cell in column E to start pasting into and then go down as far as the last populated cell in column B.

Thanks in advance
Karl.

 

[Joe4]

Can you post some sample data and show us your expected output?

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.

 

[karldugan]

Can you post some sample data and show us your expected output?

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.

FY21 Template.xlsm
	A	B	C	D	E	F	G	H
1	Lookup	Nominal	Cost Centre	Department	Period	Month	YTD
2	7000	7000
3	7010	7010
4	7040	7040
5	7041	7041
6	7100	7100
7	7200	7200
8	7201	7201
9	7210	7210
10	7220	7220
11	7300	7300
12	7301FAC	7301	FAC
13	7301	7301
14	7303FAC	7303	FAC
15	7304	7304
16	7307	7307
17	7308	7308
18	7315	7315
19	7318	7318
20	7400	7400
21	7402	7402
22	7410	7410
23	7411	7411
24	7413	7413
25	7420	7420
26	7500	7500
27	7601	7601
28	7602	7602
29	8110ORDPRO	8110	ORD	PRO
30	8425	8425
31
32
33
34
35
Prior Forecast

Cell Formulas
Range	Formula
A2:A35	A2	=B2&C2&D2

 

[karldugan]

Can you post some sample data and show us your expected output?

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.

So I want to post a date into column E, but the macro needs to find the first empty cell in column E and then paste the date down as far as the data goes in column B.

 

[Joe4]

You didn't mention exactly where the date to be used comes from, so I created an InputBox for it, though that can easily be changed.
Try this code:

Sub MyCopy()

    Dim dte As Date
    Dim firstE As Long
    Dim lastE As Long
    
'   Prompt for new date
    dte = InputBox("What date would you like to populate?")

'   First first row in column E neededing data
    firstE = Cells(Rows.Count, "E").End(xlUp).Row + 1
    
'   Find last row in E needing data (by looking at column B)
    lastE = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate column E
    Range(Cells(firstE, "E"), Cells(lastE, "E")).Value = dte
    
End Sub

 

[karldugan]

You didn't mention exactly where the date to be used comes from, so I created an InputBox for it, though that can easily be changed.
Try this code:

Sub MyCopy()

    Dim dte As Date
    Dim firstE As Long
    Dim lastE As Long
   
'   Prompt for new date
    dte = InputBox("What date would you like to populate?")

'   First first row in column E neededing data
    firstE = Cells(Rows.Count, "E").End(xlUp).Row + 1
   
'   Find last row in E needing data (by looking at column B)
    lastE = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Populate column E
    Range(Cells(firstE, "E"), Cells(lastE, "E")).Value = dte
   
End Sub

Hi Joe,
Thanks for the super speedy response, the date was picking up from another worksheet and I'd managed to copy it but couldn't figure out the next part.

That said, that prompt is really cool, so I am going to keep that instead!

Thanks for the help and it works a treat!
Karl.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[karldugan]

Hi Joe, wondering if you could help with another bit, as I really want to utilise that date prompt you've given me. What I want to do is copy the data from row 6 down based on the date that comes from your input. The data will always start in row 6, but will be various numbers of rows down between different files, all the rows contain data and there is a break line at the bottom, so I was trying to use something like the following code, I just can't figure out how I tell it to find a specific column based on the header.

Range("B6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

FY21 Template.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S
4	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22
5	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
6	£95,000	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
7	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
8	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0	£0
Forecast

Cell Formulas
Range	Formula
I4:S4	I4	=EOMONTH(H4,0)+1
I6:S6,H7:S8	I6	=Budget!H6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H6:H35,H47	Expression	=IF(H$5="ACTUAL",TRUE,)	text	NO
G47,G6:G35,I6:S35,I47:S47	Expression	=IF(G$5="ACTUAL",TRUE,)	text	NO

 

[Joe4]

Hi Joe, wondering if you could help with another bit, as I really want to utilise that date prompt you've given me. What I want to do is copy the data from row 6 down based on the date that comes from your input. The data will always start in row 6, but will be various numbers of rows down between different files, all the rows contain data and there is a break line at the bottom, so I was trying to use something like the following code, I just can't figure out how I tell it to find a specific column based on the header.

That really is a new and different question, and as such, should be posted in a new thread so it shows up on the "Unanswered threads" listing for all the community to see.

The general rule of thumb is that new questions should be posted to new threads, unless it is a clarification of the original question.