Need Help With Variable Excel VBA Data Reformatting

[sax2play]

Hello All!

I am setting up a macro to take raw data from one workbook and move it to another workbook, while putting the columns in a specific, but different order, using a configuration table. Here is a basic example of what I am trying to accomplish:

Raw Data - Workbook "wa", Sheet "Data" - starts in Range A1

Apples	Grapes	Strawberries	Blueberries	Peaches
1​	4​	3​	6​	7​
2​	5​	2​	8​	5​
3​	6​	1​	10​	3​
4​	7​	0​	12​	1​
5​	8​	-1​	14​	-1​
6​	9​	-2​	16​	-3​
7​	10​	-3​	18​	-5​
8​	11​	-4​	20​	-7​
9​	12​	-5​	22​	-9​
10​	13​	-6​	24​	-11​

Config Sheet - Workbook "WB", Sheet "Config" - text starts in Range A6

Strawberries
Grapes
Blueberries
Apples
Peaches

Output Sheet - Workbook "WB", Sheet "New Data" - starts in Range A1

Strawberries	Grapes	Blueberries	Apples	Peaches
3​	4​	6​	1​	7​
2​	5​	8​	2​	5​
1​	6​	10​	3​	3​
0​	7​	12​	4​	1​
-1​	8​	14​	5​	-1​
-2​	9​	16​	6​	-3​
-3​	10​	18​	7​	-5​
-4​	11​	20​	8​	-7​
-5​	12​	22​	9​	-9​
-6​	13​	24​	10​	-11​

Here is the code I currently have:

    Dim last_row, last_col As Long
    Dim includedCol As Integer
    Dim rngFound As Range
    Dim Col As Integer
           
    wa.Sheets("Data").Select
    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    last_col = Cells(1, Columns.Count).End(xlToLeft).Column
    includedCol = 6
    colString = ""
    Col = 1
   
    Do While WB.Sheets("Config").Cells(includedCol, 1) <> ""
   
        'If WB.Worksheets("Config").Cells(includedCol, 1) = "" Then
            Set rngFound = wa.Sheets("Data").Range("1:1").Find(What:=WB.Sheets("Config").Cells(includedCol, 1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
        'End If
               
        If Not rngFound Is Nothing Then
            WB.Sheets("New Data").Range(Cells(1, Col), Cells(last_row, Col)).Value = wa.Sheets("Data").Range(Cells(1, rngFound.Column), Cells(last_row, rngFound.Column)).Value
           
        End If
        Col = Col + 1
        includedCol = includedCol + 1
       
    Loop

The code runs fine up until the line:
WB.Sheets("New Data").Range(Cells(1, Col), Cells(last_row, Col)).Value = wa.Sheets("Data").Range(Cells(1, rngFound.Column), Cells(last_row, rngFound.Column)).Value

Then I get a Run-time error 1004: Application-defined or object-defined error.

Hoping someone here can help straighten me out!!

Thanks in advance!

 

[sax2play]

I've also tried changing the line that errors out to:

If Not rngFound Is Nothing Then
            WB.Sheets("New Data").Range("R1C" & Col & ":R" & last_row & "C" & Col).Value = wa.Sheets("Data").Range("R1C" & rngFound.Column & ":R" & last_row & "C" & rngFound.Column).Value

I get the same run-time error 0004.....

 

[Gokhan Aycan]

Is this a legal syntax?

Set rngFound = wa.Sheets("Data").Range("1:1").Find(

 

[Alex Blakenburg]

Any reason you have 2 workbook variables ie wa and WB ?
In the below I am just using wa.
Also I found "includedCol" a bit confusing since you are actually referring to rows.

In the line erroring out, the Cells needs to know which sheet it is referring to, when it is not referring to the active sheet.
I ended up specifying both but you didn't need to.

Sub test()

    Dim last_row, last_col As Long
    Dim includedCol As Integer
    Dim rngFound As Range
    Dim Col As Integer
    
    'XXX Added XXX
    Dim wa As Workbook
    Dim shData As Worksheet
    Dim shNew As Worksheet
        
    Set wa = ActiveWorkbook
    Set shData = wa.Worksheets("Data")
    Set shNew = wa.Worksheets("New Data")
    '---------------
           
    wa.Sheets("Data").Select
    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    last_col = Cells(1, Columns.Count).End(xlToLeft).Column
    includedCol = 6
    colString = ""
    Col = 1
   ' WB to wa
    Do While wa.Sheets("Config").Cells(includedCol, 1) <> ""
   
        'If WB.Worksheets("Config").Cells(includedCol, 1) = "" Then
            Set rngFound = wa.Sheets("Data").Range("1:1").Find(What:=wa.Sheets("Config").Cells(includedCol, 1), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=True)
        'End If
               
        If Not rngFound Is Nothing Then
            ' XXX Changed
            shNew.Range(shNew.Cells(1, Col), shNew.Cells(last_row, Col)).Value = shData.Range(shData.Cells(1, rngFound.Column), shData.Cells(last_row, rngFound.Column)).Value
           
        End If
        Col = Col + 1
        includedCol = includedCol + 1
       
    Loop
    
End Sub

 

[sax2play]

Hey Alex, thanks for the reply! This is just part of a much larger macro. During this step, I open an exported file with all of the raw data and then reformat it into a different workbook, which is why I have two workbooks as variables. The IncludedCol variable is the column that I want to include in the new report, even though it is looping through rows in a table lol.

I will take a look at your corrections after I get out of a meeting

 

[sax2play]

This worked perfect, thanks Alex!!

I redefined the Worksheet variables to differentiate between the two workbooks and then defined the Cells in the formula like you showed and it works perfect.

Thanks again!!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.