Importing Data with Dynamic Columns (VBA)

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
176
Office Version
  1. 365
Platform
  1. Windows
Good day,

I'm trying to come up with VBA code to import a few columns of data from an exported spreadsheet. The challenge is that the amount of columns and even the columns the data end up in are subject to change each time the report is pulled. There is 1 consistent factor though that I think I can use to pull the remaining data.

Each time the sheet is exported there is a header above the columns that are merged across all the columns I need to pull. "Rate Heading" is the header for that, so how would I write VBA to specifically find the Rate Heading section of the report and then pull all the data below that merged column?

Currently, this is what I'm working with which I pulled from another report I have that needed something similar but not as complex.

VBA Code:
Sub DiscountPer()
   Application.DisplayAlerts = False
   
   Dim Fnd As Range
   Dim Hrg As Range
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   
   Set wkbCrntWorkBook = ActiveWorkbook
   
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      .Show
      
      If .SelectedItems.Count > 0 Then
        Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
        Set Fnd = Range("A1:AG1").Find("Rate Heading", , , xlPart, , , False, , False)
        Range(Fnd.Offset(1), Range("N2000")).Copy
        wkbCrntWorkBook.Sheets("RC1").Range("A1").PasteSpecial xlPasteValues
        wkbCrntWorkBook.Sheets("RC1").Range("A1").Value = ActiveSheet.Range("B13").Value
        wkbSourceBook.Close False
      End If
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Oddball2020

Board Regular
Joined
May 6, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Good day,

I'm trying to come up with VBA code to import a few columns of data from an exported spreadsheet. The challenge is that the amount of columns and even the columns the data end up in are subject to change each time the report is pulled. There is 1 consistent factor though that I think I can use to pull the remaining data.

Each time the sheet is exported there is a header above the columns that are merged across all the columns I need to pull. "Rate Heading" is the header for that, so how would I write VBA to specifically find the Rate Heading section of the report and then pull all the data below that merged column?

Currently, this is what I'm working with which I pulled from another report I have that needed something similar but not as complex.

VBA Code:
Sub DiscountPer()
   Application.DisplayAlerts = False
 
   Dim Fnd As Range
   Dim Hrg As Range
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
 
   Set wkbCrntWorkBook = ActiveWorkbook
 
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      .Show
  
      If .SelectedItems.Count > 0 Then
        Set wkbSourceBook = Workbooks.Open(.SelectedItems(1))
        Set Fnd = Range("A1:AG1").Find("Rate Heading", , , xlPart, , , False, , False)
        Range(Fnd.Offset(1), Range("N2000")).Copy
        wkbCrntWorkBook.Sheets("RC1").Range("A1").PasteSpecial xlPasteValues
        wkbCrntWorkBook.Sheets("RC1").Range("A1").Value = ActiveSheet.Range("B13").Value
        wkbSourceBook.Close False
      End If
Use this code to find "Rate Header" in Row 1. Change it as needed (RTH can be whatever you want)

Dim RTH as Long
RTH = WorksheetFunction.Match("Rate Header", Rows(1), 0)

This will return the column number that matches Rate Header. Then use that to copy such as

Columns(RTH).Copy

Or say if it was 2 more columns beyond Rate Header you wanted to copy as well,

Range(Columns(RTH), Columns(RTH+2)).Copy

Or if it was just a subset range in those columns,

Range(Cells(2,RTH), Cells(100, RTH+2)).Copy

.... which is using Cells(Rows,Columns) format.... so Range("A1") = Cells(1,1).... or Range("B4") = Cells(4, 2) .... Range("A1:B4") = Range(Cells(1,1), Cells(4,2)) etc this is handy and easier in VBA to use numbers derived from the MATCH function above, and preferred when making VBA dynamic such as in your case with a floating header

then paste as normal wherever

Similar for looking down a column,

WHERERU = WorksheetFunction.Match("Find Me", Columns(1), 0)

Sometimes merged cells though gives VBA fits when trying to do things like copying
 
Last edited:

Forum statistics

Threads
1,143,638
Messages
5,719,977
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top