pasting cell number of times

**rockstart** · 02-24-2011, 03:40 PM

I have picked up to write macro after a long time. So please bear with me here. I have following data

Date Name
2/22/11 A
XXXXXX B
XXXXXX C
XXXXXX D

Ignore XXXXXX read them as blank cells (added to keep formatting of post)

The end state I want to get to is

Date Name
2/22/11 A
2/22/11 B
2/22/11 C
2/22/11 D

Now I have figured out how to count # of rows in 2nd column and put it in a variable "RecordCount" (Which will be 4 in this case) my question is how can I copy the data in cell A2 and paste it to next 3 cells below?

**texasalynn** · 02-24-2011, 03:45 PM

is it always only three rows or will that vary? If it varies how is that determined?

**rockstart** · 02-24-2011, 04:12 PM

Originally Posted by texasalynn

is it always only three rows or will that vary? If it varies how is that determined?

It varies and that is why I have found a way to do that count using the following function

RecordCount = Application.WorksheetFunction.CountA(Range(Range("F8"), Range("F8").End(xlDown)))

Now my RecordCount variable tells me how many rows I need to paste data.

**watersev** · 02-24-2011, 04:30 PM

hope that helps:

Please Login or Register  to view this content.

that pastes "test" into the range K1:K4

Just FYI in order to select blank cells you can also use specialcells method (I do not know if its applicable in your case), see VB help for details

**rockstart** · 02-24-2011, 04:33 PM

Originally Posted by watersev

hope that helps:

Please Login or Register  to view this content.

that pastes "test" into the range K1:K4

Thanks watersev. The issue I have is K1 is fixed but K4 is dynamic. I have that number stored in a variable so I need a dynamic function that can take my variable as parameter and then copy the data.

is there a syntax for say
[k1:k(variable_name)] = "test"

**watersev** · 02-24-2011, 04:36 PM

is it all data you have or it's a sample with some more similar data going down?

**rockstart** · 02-24-2011, 04:41 PM

Originally Posted by watersev

is it all data you have or it's a sample with some more similar data going down?

Its a sample of data I have. Basically I am consolidating data from different files into one spread sheet so every time I open a spread sheet I need to count the number of rows for NAME column and copy them over. Then the date field is only populated once in source sheet so when I copy it over I need to paste it against every name. Example one spread sheet has 4 names and othe other has 8. Next time I get another one I am not sure how many it can have it could range anywhere from 1 - 1000's

**watersev** · 02-24-2011, 04:42 PM

if it is:
assuming Name is column B and data starts from second row and has no empty cells within table, it can be done this way without worksheet.function:

Please Login or Register  to view this content.

[a1] contains value we need to populate down the column A

**watersev** · 02-24-2011, 04:44 PM

if you can provide a sample workbook we could look at it together and find suitable solution for you

**rockstart** · 02-24-2011, 05:29 PM

Originally Posted by watersev

if it is:
assuming Name is column B and data starts from second row and has no empty cells within table, it can be done this way without worksheet.function:

Please Login or Register  to view this content.

[a1] contains value we need to populate down the column A

This is what worked for me. Its crude but did the trick

For i = 2 To RecordCount + 1
Range("A" & i).Select
ActiveSheet.Paste
Next i

pasting cell number of times

LinkBack

Thread Tools

Rate This Thread

Display

pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Re: pasting cell number of times

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions