Macro paste value not formula help

[maku]

I've pieced together a macro that goes through all my worksheets and pastes data from worksheets that contain data tables to a Master Data worksheet.


The problem is, I have formulas in some of the data table on the individual worksheets, but don't want the formula copied from that worksheet to the Master Data table, I just want the actual value that is shown on that worksheet to be copied.


Here is my code (that copies data & formulas), but I don't know if I need to do a Paste Special somewhere in it to achieve this.


Thank you,

-Maku

ps- the end part of the code referencing the parameters sheet is copying the header to the master data table.

[pre]

Option Explicit
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim FValue As String
Dim i As Long
Dim lrow As Long

Sub ConsolidateMasterData()

Application.ScreenUpdating = False

FValue = "Year"

Set wsMain = Worksheets("Master Data")
wsMain.Cells.Clear

lrow = Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To Worksheets.Count
With Worksheets(i)
If .Name <> "Master Data" Then
lrow = .Range("G" & .Rows.Count).End(xlUp).Row
If .Range("G2").Value <> "" Then
If .Range("G1") = FValue Then
.Range("G2:AB" & lrow).Copy Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End If
End If
End With
Next i

Sheets("parameters").Range("AA1:AV1").Copy _
Destination:=Sheets("Master Data").Range("A1")

Application.ScreenUpdating = True

End Sub

[/pre]

 

[SirJB7]

Hi, Maku!


Replace the statement:

.Range("G2:AB" & lrow).Copy Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

by:

.Range("G2:AB" & lrow).Select

Selection.Copy

Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Regards!

 

[Luke M]

SirJB7, why do all that extra selecting? =)

[pre]

.Range("G2:AB" & lrow).Copy
Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[/pre]

 

[maku]

SirJB7,


I thought it was PasteSpecial that was needed, but didn't know how to separate the syntax out. Thank you!


Your code didn't quite work, it created a Run-Timer error '1004' Select Method of Range Class Failed.


I altered your code adjustment a little, and it works as desired.


.Range("G2:AB" & lrow).Copy

Worksheets("Master Data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


THanks again for the assistance.

-Maku


ooohh...I see Luke beat me too it, I guess that's because I worked on trial and error and Luke is the master


Seriously though, thank you, I was able to get it with your help.

 

[Luke M]

Glad you were able to figure it out on your own. =)

If nothing else, I find it usually helps me learn/remember something better that way.