Replacing copy and paste in VB Code

[gt213]

Hi,

I've just registered with the site and this is my first post, I have various forums and posts as helpful resources in the past so thank you all for that!

I have some VB code which basically takes values from an input list and pastes them into a calculation sheets, where the results are copied and pasted as values on another sheet, this is repeated until all the values in the input list have been used. The code is taking a while to run so I want to speed it up by getting rid of the copy and paste, and use '.Value' instead which I've seen suggested on other posts, but I can't get it to work with mine.

Here is the relevant part of the code:

Note: 'PasteRowIndex'=1

For Each r In Range("B2", Range("B" & Rows.Count).End(xlUp))
Sheets("Single Account").Range("B1").Value = r.Value

Sheets("Single Account").Select
Rows("1:49").Select
Selection.Copy
Sheets("Results").Select
Rows(pasteRowIndex).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
pasteRowIndex = pasteRowIndex + 49
Sheets("Lookups").Select

Thanks for any help!

 

[cerfani]

Hi,

I've just registered with the site and this is my first post, I have various forums and posts as helpful resources in the past so thank you all for that!

I have some VB code which basically takes values from an input list and pastes them into a calculation sheets, where the results are copied and pasted as values on another sheet, this is repeated until all the values in the input list have been used. The code is taking a while to run so I want to speed it up by getting rid of the copy and paste, and use '.Value' instead which I've seen suggested on other posts, but I can't get it to work with mine.

Here is the relevant part of the code:

Note: 'PasteRowIndex'=1

For Each r In Range("B2", Range("B" & Rows.Count).End(xlUp))
Sheets("Single Account").Range("B1").Value = r.Value

Dim source As Range
Set source = Sheets("Single Account").Rows("1:49")
Sheets("Results").Rows(pasteRowIndex).Value2 = source.Value2
pasteRowIndex = pasteRowIndex + 49
Sheets("Lookups").Select

Thanks for any help!

i edited your code... not tested but that might work

also you should avoid using the macro recorder style of selecting stuff and make explicit references to excel objects

when you write a macro you do not need to emulate user input

 

[gt213]

Hi,

Thanks for your help, although that doesn't seem to quite work.

It looks like only the values in the first row from "Single Account" are being copied over to the "Results" sheet, and also a formula in row 1 doesn't get updated with each new value for: Sheets("Single Account").Range("B1").Value = r.Value

 

[cerfani]

Hi,

Thanks for your help, although that doesn't seem to quite work.

It looks like only the values in the first row from "Single Account" are being copied over to the "Results" sheet, and also a formula in row 1 doesn't get updated with each new value for: Sheets("Single Account").Range("B1").Value = r.Value

try... (i made the area being copied to the same size as the area copying from and i referenced Formula property in your first loop)

For Each r In Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp)) 'i prefer using Cells and not evaluating strings in a range function
Sheets("Single Account").Range("B1").Formula= r.Formula 'i dont understand why you do this... you keep putting new values in B1 but if that is what you want then i guess it is fine

Dim source As Range
Set source = Sheets("Single Account").Rows("1:49")
Sheets("Results").Range(pasteRowIndex & ":" & (pasteRowIndex + 48)).Value2 = source.Value2
pasteRowIndex = pasteRowIndex + 49
Sheets("Lookups").Select

if it doesn't work then just keep tweaking it... get use to stepping through your code and debugging... also get use to the errors you run into. The only way to learn vba is to write bad code first

 

[gt213]

Thanks a lot, I'll play around with it and see what I can get.

 

[cerfani]

Thanks a lot, I'll play around with it and see what I can get.

yeah and feel free to ask if you hit a wall