VBA to copy data from one worksheet to another (only specific columns)

[SpireiteSteve]

Hello all,

I am new to VBA and have done everything I can think of to find a solution online. A few nearly moments but nothing quite doing the trick.

I have 1 workbook containing multiple worksheets. In one worksheet(worksheet A) I have 40 columns, not all of which are needed. Another worksheet (worksheet B) contains just the header rows I need (10 of them), the rest of that worksheet is blank.

What I would like to do is copy all row data from worksheet A (headers here are in row 3) into worksheet B (headers are in column 1). There are also quite a few formulas in worksheet A and so I want the copy to worksheet B to paste only values and formulas, not the actual formulas.

Looking through many previous posts I thought I had found the solution, but it pasted formulas, not just the data and formatting that I need. I tried tweaking the code and ended up with this:

Sub CopyCols()
Application.ScreenUpdating = False
Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
Set srcWS = Sheets("Current_Receivables_Aging_Detai")
Set desWS = Sheets("Consolidated")
LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
Set foundHeader = srcWS.Rows(3).Find(header, LookIn:=xlValues, lookat:=xlWhole)
If Not foundHeader Is Nothing Then
srcWS.Range(srcWS.Cells(3, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy
desWS.Cells(2, header.Column).PasteSpecial xlPasteValuesAndFormatting
End If
Next header
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The row in bold appears to be problematic, error message to debug the code.

One last thing I should add, the dataset (worksheet A) is quite large (up to 30,000 rows), just thought I would mention in case the code above isn't the most efficient?

I am completely stuck, any help would be really appreciated.

Thanks in advance for anyone taking their time to try and help me here.

 

[Micron]

Retype the line and look at the paste options. I don't see the one you used in the drop down list.
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[SpireiteSteve]

Retype the line and look at the paste options. I don't see the one you used in the drop down list.
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

I actually found another solution so sorry but thanks for the response and also information. I will be sure to remember this advice if (most likely when) I come across another problem.