VLookup in VBA?

[golf4wff]

Hi, Everyone -

I've been gone for a while, but back now...

I have a question that I've been going crazy. I'm working on our
Payroll Sheet for work, and the situation is the following:

On the sheet DATA, I've put together VLookup table with all the
employees and their specific data on budget percentages on hours
worked. For example, one row would include Sally Smith's name, dept.,
an amount equal to 50% of hours worked in Program A, 50% of hours
worked in Program B, etc.

With some great help, I've come up with the following code:

Private Sub CommandButton3_Click()
If Sheets("DATA").Range("A1").Value = 4 Then
Sheets("PAYROLL SHEET").Range("A40:AL40").Clear
Sheets("DATA").Range("B6:V6").SpecialCells(xlCellTypeVisible).Copy
Destination:=Sheets("payroll sheet").Range("B40")
Sheets("PAYROLL SHEET").Range("A41:AL41").Clear
Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy
Destination:=Sheets("payroll sheet").Range("B41")

End If
End Sub

The code seems to copy the data from the table fine, but because I
have some formulas in the table instead of just values, I'm getting
#REF entries in the copied data.

My question is: is there a way to modify my code so that the formula
results (resulting values), in the tables, gets copies as to get rid
of the #REF errors? Maybe using a PasteSpecial method in the code?

Thanks for your help.

Frank

 

[Wigi]

Hi

For instance

Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy
Sheets("payroll sheet").Range("B41").PasteSpecial xlValues

 

[golf4wff]

Hi

For instance

Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy
Sheets("payroll sheet").Range("B41").PasteSpecial xlValues

--
Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music















- Show quoted text -

Hi, Wigi -

Thanks so much for the help!!! That did the trick. Can I ask you a few
more follow-up questions?

I have both the table cells and the destination cells formatted as 17
pitch font and centered/wrapped, but when the data is copied, it seems
to reformat the destination cells as 10 pitch font and left-justified/
no wrap. Is there a way to modify my code to correct this?

My second question is that, when the data is copied, I look at the
table and it appears that the data that WAS copied is still surrounded
by the "marching ants": (ya know when you copy something?) Is there a
way to revise my code to reflect that, once the data is copied over,
the ants around the copied data goes away?

THANKS SO MUCH FOR THE HELP!!!

Frank

 

[Don Guillett]

with Sheets("payroll sheet").Range("B41")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
end with

application.cutcopymode=false

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi

For instance

Sheets("DATA").Range("H76:AR76").SpecialCells(xlCellTypeVisible).Copy
Sheets("payroll sheet").Range("B41").PasteSpecial xlValues

--
Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music















- Show quoted text -

Hi, Wigi -

Thanks so much for the help!!! That did the trick. Can I ask you a few
more follow-up questions?

I have both the table cells and the destination cells formatted as 17
pitch font and centered/wrapped, but when the data is copied, it seems
to reformat the destination cells as 10 pitch font and left-justified/
no wrap. Is there a way to modify my code to correct this?

My second question is that, when the data is copied, I look at the
table and it appears that the data that WAS copied is still surrounded
by the "marching ants": (ya know when you copy something?) Is there a
way to revise my code to reflect that, once the data is copied over,
the ants around the copied data goes away?

THANKS SO MUCH FOR THE HELP!!!

Frank

 

[golf4wff]

with Sheets("payroll sheet").Range("B41")
   .PasteSpecial Paste:=xlPasteValues
   .PasteSpecial Paste:=xlPasteFormats
end with

application.cutcopymode=false

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






Hi, Wigi -

Thanks so much for the help!!! That did the trick. Can I ask you a few
more follow-up questions?

I have both the table cells and the destination cells formatted as 17
pitch font and centered/wrapped, but when the data is copied, it seems
to reformat the destination cells as 10 pitch font and left-justified/
no wrap. Is there a way to modify my code to correct this?

My second question is that, when the data is copied, I look at the
table and it appears that the data that WAS copied is still surrounded
by the "marching ants": (ya know when you copy something?) Is there a
way to revise my code to reflect that, once the data is copied over,
the ants around the copied data goes away?

THANKS SO MUCH FOR THE HELP!!!

Frank- Hide quoted text -

- Show quoted text -

Hi, Don -
Thanks for the guidance - THAT DID THE TRICK!!!

Thanks again.

Frank

 

[Don Guillett]

Glad to help. Most of us here prefer TOP posting so we don't have to scroll
down for the reply

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

with Sheets("payroll sheet").Range("B41")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
end with

application.cutcopymode=false

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






Hi, Wigi -

Thanks so much for the help!!! That did the trick. Can I ask you a few
more follow-up questions?

I have both the table cells and the destination cells formatted as 17
pitch font and centered/wrapped, but when the data is copied, it seems
to reformat the destination cells as 10 pitch font and left-justified/
no wrap. Is there a way to modify my code to correct this?

My second question is that, when the data is copied, I look at the
table and it appears that the data that WAS copied is still surrounded
by the "marching ants": (ya know when you copy something?) Is there a
way to revise my code to reflect that, once the data is copied over,
the ants around the copied data goes away?

THANKS SO MUCH FOR THE HELP!!!

Frank- Hide quoted text -

- Show quoted text -

Hi, Don -
Thanks for the guidance - THAT DID THE TRICK!!!

Thanks again.

Frank