Copy Columns From Opened File into Active Worksheet, Last Two Columns Don't Paste

[SkywardPalm]

I have VBA code that pulls the column data from one workbook into another, and it pulls all but the last two columns based on headers.

Sub Import_Data()
    ' Name                  Type                    Description
    '==================================================================================================
    Dim wbCurrent           As Workbook             ' Current workbook
    Dim wksCurrent          As Worksheet            ' Current sheet
    Dim rCurrentColHeaders  As Range                ' Current column headers

    Dim rCurVarColHeaders   As Range                ' Current Variance column headers (which will be searched through)
    
    Dim rColHead            As Range                ' Iterates through the Imported column headers
    Dim rMatchColHead       As Range                ' Gets the matching Variance column header
    
    Dim iNumCellsPerColumn  As Long                 ' Defines how many cells per column we're copying
    
    Dim CurrentFileToOpen As Variant
    
    ' init
    '==========================================
    ' headers
    Set rCurVarColHeaders = ThisWorkbook.Worksheets("Current").Range("A1:O1")
    ThisWorkbook.Worksheets("Current").Rows("2:" & Rows.Count).ClearContents
    ThisWorkbook.Worksheets("Variance").Rows("2:" & Rows.Count).ClearContents
    '
'
'   Import Current Master Data
'
    CurrentFileToOpen = Application.GetOpenFilename(Title:="Browse for Current Draft File", FileFilter:=" Excel Files(*.xls*),*xls*")
    If CurrentFileToOpen <> False Then
        Set wbCurrent = Application.Workbooks.Open(CurrentFileToOpen)
        Set wksCurrent = wbCurrent.Worksheets("Master")
        Set rCurrentColHeaders = wksCurrent.Range("A1:AZ1")

        ' set number of cells to copy per column (change to suit your needs)
        With wbCurrent.Worksheets("Master")
            iNumCellsPerColumn = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        
        ' process
        '==========================================
        ' - loop through the Current column header cells
        ' -- try to find the matching column
        ' -- if a match is found, copy column cells from the Current to the Variance
        For Each rColHead In rCurrentColHeaders
            Set rMatchColHead = rCurVarColHeaders.Find(rColHead.Text, , xlValues, xlWhole)
            If Not (rMatchColHead Is Nothing) Then
                wksCurrent.Range(rColHead, rColHead.Offset(iNumCellsPerColumn, 0)).Copy
                rMatchColHead.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Application.CutCopyMode = False
            Else
                Debug.Print rColHead & " Header Not Found"
            End If
        Next rColHead
        wbCurrent.Close False
    End If
End Sub

The last two columns are both price columns that could be formulas.. the values are not pasting into the destination, though. Is there something I could be doing better or may be missing to pull this data?

 

[StephenCrump]

There's nothing obviously wrong that jumps out ...

Presumably you've got the right header range: Set rCurrentColHeaders = wksCurrent.Range("A1:AZ1")?

And you're not getting any " Header Not Found" messages?

Assuming your "last two columns" are AY1 and AZ1, perhaps try: Set rCurrentColHeaders = wksCurrent.Range("AY1:AZ1") and step through the code to see what's happening?

 

[SkywardPalm]

There's nothing obviously wrong that jumps out ...

Presumably you've got the right header range: Set rCurrentColHeaders = wksCurrent.Range("A1:AZ1")?

And you're not getting any " Header Not Found" messages?

Assuming your "last two columns" are AY1 and AZ1, perhaps try: Set rCurrentColHeaders = wksCurrent.Range("AY1:AZ1") and step through the code to see what's happening?

Thanks for checking out the syntax. Here is an example of the sheet that is being filled out prior to pulling data..

Variance Report.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	LOC	REC	NDC	QTY	TRADE	GENERIC	STRENGTH	PACKAGE SIZE	DOSE FORM	MANUFACTURER	DEA CLASS	AHFS	SOURCE	Package Cost	Extended
2
Current

Here is an example of the same file after data is pulled from the target file..

Variance Report.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	LOC	REC	NDC	QTY	TRADE	GENERIC	STRENGTH	PACKAGE SIZE	DOSE FORM	MANUFACTURER	DEA CLASS	AHFS	SOURCE	Package Cost	Extended
2	0024-SCRIPT PRO MACHINE	Chaz061	65862037310	408.00	ESCITALOPRAM OXALATE	ESCITALOPRAM OXALATE	5MG/1	100	ORAL TABLET, FILM COATED	AUROBINDO PHARMA LIMITED		28160420-ANTIDEPRESSANTS SELECTIVE SEROTONIN-REUPTAKE INHIBITORS
3	0014-ALPHA BAY #4	JMM109	47335072183	30.00	FEBUXOSTAT	FEBUXOSTAT	40MG/1	30	ORAL TABLET, FILM COATED	SUN PHARMACEUTICAL INDUSTRIES INC.		92160000-ANTIGOUT AGENTS
4	0014-ALPHA BAY #4	DCR152	51991062328	10.00	ESTRADIOL AND NORETHINDRONE ACETATE	ESTRADIOL; NORETHINDRONE ACETATE	.5; .1MG/1; MG/1	28	TABLET	BRECKENRIDGE PHARMACEUTICAL, INC.		68160400-ESTROGENS	A: Inventory Price File
5	0014-ALPHA BAY #4	DCR153	51991047428	8.00	ESTRADIOL AND NORETHINDRONE ACETATE	ESTRADIOL; NORETHINDRONE ACETATE	1; .5MG/1; MG/1	28	TABLET	BRECKENRIDGE PHARMACEUTICAL, INC.		68160400-ESTROGENS	A: Inventory Price File
Current

And below is an example of the data that is being imported based on the header. The file will be dynamic and the headers may be in different orders. The Package Cost and the Extended columns don't seem to be pulling in, it does print that the Package Cost and Extended Headers were not found.... Sometimes the cells hold dollar prices, sometimes it may have a formula that I need the values from.. There's no problems with columns that are hidden in the Current file.. I can't tell what is preventing the headers from being recognized, if that may be stopping the data from pulling.

CURRENT FILE.xlsm
	A				E	F	G	H	I	J	K		M		O	P			S	T		V	W	X	Y	Z	AA	AB
1	LOC				NDC	Scanned NDC	QTY	MIS Divisor	MIS Count Method	Item Number	Med Desc		TRADE		STRENGTH	PACKAGE SIZE			DOSE FORM	MANUFACTURER		DEA CLASS	AHFS	SOURCE	Package Cost	Unit Cost	Extended	$ 2,720,628.74
2	0024-SCRIPT PRO MACHINE				65862037310		408.00	100	TAB		ESCITALOPRAM OXALATE 5MG/1 ORAL TABLET, FILM COATED		ESCITALOPRAM OXALATE		5MG/1	100			ORAL TABLET, FILM COATED	AUROBINDO PHARMA LIMITED			28160420-ANTIDEPRESSANTS SELECTIVE SEROTONIN-REUPTAKE INHIBITORS		$ -	$ -	$ -
3	0014-ALPHA BAY #4				47335072183		30.00	30	TAB		FEBUXOSTAT 40MG 30		FEBUXOSTAT		40MG/1	30			ORAL TABLET, FILM COATED	SUN PHARMACEUTICAL INDUSTRIES INC.			92160000-ANTIGOUT AGENTS		$ 98.03	$ 3.27	$ 98.03
4	0014-ALPHA BAY #4				51991062328		10.00	1	BLISTER	2146454	ESTRAD+NOR0.5/0.1MGBPBRE1X28@ 1		ESTRADIOL AND NORETHINDRONE ACETATE		.5; .1MG/1; MG/1	28			TABLET	BRECKENRIDGE PHARMACEUTICAL, INC.			68160400-ESTROGENS	A: Inventory Price File	$ 98.19	$ 98.19	$ 981.90
5	0014-ALPHA BAY #4				51991047428		8.00	1	BLISTER	1753169	ESTRADIOL-NORETH 1-0.5 MG TAB 1		ESTRADIOL AND NORETHINDRONE ACETATE		1; .5MG/1; MG/1	28			TABLET	BRECKENRIDGE PHARMACEUTICAL, INC.			68160400-ESTROGENS	A: Inventory Price File	$ 54.75	$ 54.75	$ 438.00
6	0026-UNIT OF USE BAY #1				51862025801		4.00	1	BLISTER	2324648	NEXTSTELLIS 3-14.2 MG TABLET 1		NEXTSTELLIS		3-14.2MG	28			ORAL TABLET					A: Inventory Price File	$ 179.84	$ 179.84	$ 719.36
7	0041-ALPHA BAY 9				00456150055		2.00	1	BLISTER	2480333	SAVELLA TAB 4WK TITRAT PK KIT 1		SAVELLA		12.5-25-50	55			TAB DS PK	ALLERGAN, INC.			28400000-FIBROMYALGIA AGENTS	A: Inventory Price File	$ 383.61	$ 383.61	$ 767.22
8	0026-UNIT OF USE BAY #1				00642747001	00642747001	7.00	1	BLISTER	3972874	SLYND 4 MG TABLET 1		SLYND		4MG/1	28			TABLET	EXELTIS USA, INC.			68120000-CONTRACEPTIVES	A: Inventory Price File	$ 183.62	$ 183.62	$ 1,285.34
Master

Cell Formulas
Range	Formula
AB1	AB1	=SUM(AA:AA)
Z2:Z8	Z2	=IFERROR(Y2/H2,"Error")
AA2:AA8	AA2	=IFERROR(Z2*G2,"Error")

 

[SkywardPalm]

I've noticed that those specific column headers are in Accounting format, which may be the issue.. if that were the issue, is there a way to bypass the format and treat it like text without making changes to the reference file?

 

[SkywardPalm]

I've noticed that those specific column headers are in Accounting format, which may be the issue.. if that were the issue, is there a way to bypass the format and treat it like text without making changes to the reference file?

I solved the issue by changing the Headers in the Variance file to Accounting format, so it will pull the headers from the Current file with no problem. Is there a better way to resolve this?

 

[StephenCrump]

So you should be getting two " Header Not Found" messages. You could consider using MsgBox, rather than Debug.Print, to make this message more in-your-face.

The cell formatting shouldn't be an issue. Based on what you've posted, the mismatch in these two headers occurs because of leading/trailing spaces.

Your code could apply the TRIM function to both source and target headers to eliminate this possibility.