VBA, For each loop, write to another sheet.

[jrwrita]

Hi guys,

I have this code, I get invalid procedure call or argument. What am I missing here?
I am trying to take the cells from MST sheet and go to another sheet. but I want to loop through each row in column K then fill in col H in my other sheet.
Any help would be appreciated.

ActiveSheet.Select


Dim cell As Range
For Each cell In Range("K8:K100")
Sheets("MST").cell.value = Sheets("MST - FrontPage").Cells("H10:H20").value
Next cell

 

[Joe4]

ActiveSheet.Select

This line serves no purpose. The "ActiveSheet" is already selected - that is what makes it active!

The ranges you are setting are not the same size, and it looks like your variable assignment is backwards.
So, you are looping through cells K8:K100 on the "MST" sheet, where exactly does the value from K8 go on your "MST - FrontPage" sheet? H10?
What about K9? Does that go to H11?
If you follow that pattern all the way through, the last populated cell would be H102. Is that correct?
If not, explain in detail exactly what should happen.

 

[jrwrita]

cells within K8:K100 can change, ie may be filled to K50, may be filled to K20, etc.
I want these to go to my FrontPage sheet in order starting at K8 to H10, K9 will go to H:11 and so on.
You are correct with what you are saying but all cells in MST K8 to K100 may not always be filled all the way to k100.

 

[Joe4]

No loop is necessary (and loops actually are slow and inefficient).

We can dynamically find the last row in column K with data, and copy down to there.
Here is what that code would look like:

    Dim lr As Long
'   Find last row in column K
    lr = Sheets("MST").Cells(Rows.Count, "K").End(xlUp).Row
'   Copy range to other sheet
    Sheets("MST").Range("K8:K" & lr).Copy Sheets("MST - Front Page").Range("H10")

 

[jrwrita]

I am getting subscript out of range on the last line, perhaps I need to increase my range in col H?

 

[Joe4]

That shouldn't be a problem. When pasting, you only need to tell it the start position.
However, you could try this and see if it makes a difference:

Sheets("MST").Range("K8:K" & lr).Copy Sheets("MST - Front Page").Range("H10:H" & lr+2)

If that still returns the error, than that is not the issue, because now the ranges are the exact same size. It means that there is probably another issue.
Things to check for:
- are the sheet names exactly correct?
- do you have any merged cells in these columns?
- do you have any protected cells in these columns?
- what is your value of "lr" (you can get this by adding a MsgBox row to your code right after the "lr" calculation?, i.e.

MsgBox lr

 

[jrwrita]

Thanks it works,
What if I want to copy two cols from "MST" page and paste to H10 and another col?

 

[Joe4]

What if I want to copy two cols from "MST" page and paste to H10 and another col?

Its simpler than you think. Just modify the column references in that copy statement, i.e.
if you were copying columns K&L to H&I, simply make the following changes.

Sheets("MST").Range("K8:[COLOR=#ff0000][B]L[/B][/COLOR]" & lr).Copy Sheets("MST - Front Page").Range("H10:[COLOR=#ff0000][B]I[/B][/COLOR]" & lr+2)

 

[jrwrita]

Here is my current code:

 Sheets("MST").Range("K8:L" & lr).Copy
    Sheets("MST - FrontPage").Range("E11").PasteSpecial xlPasteValuesAndNumberFormats

Your K8:L suggestion is good cuz it gets what I want. But pasting it into E:11:I seems wrong. I dont want the data next to each other I want something like E:11 and then col L to K. Right now col L is going to col F.

 

[Joe4]

If you are not posting the two columns right next to each other, then I would recommend doing it in two separate steps.
Keep the original structure we had, and have it copy column K to column E.
Then, create a copy of that same line, changing to to copy column L to column L.