Import Rows Based on Heading

T

trsisko

Board Regular
Joined
May 20, 2008
Messages
176
I want to create an Import Button.

The Button will Import Rows Based on the Heading SalType/Salary. and Import 100 Rows and Three Columns underneath it.

The Problem is the worksheets will be Random and I will not know the names.

In addition to this I'd Also have Row's where Salaries = 0 therefore I'd want those removed. Maybe with a text msg.

Is this even possible?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
trsisko said:
I want to create an Import Button.

The Button will Import Rows Based on the Heading SalType/Salary. and Import 100 Rows and Three Columns underneath it.

The Problem is the worksheets will be Random and I will not know the names.

In addition to this I'd Also have Row's where Salaries = 0 therefore I'd want those removed. Maybe with a text msg.

Is this even possible?
Click to expand...
This is not clear to me..

1. "Import 100 Rows and Three Columns underneath it" I can picture 100 rows ... but not with three columns underneath. Can you clarify the layout.

2. Where are we importing from?

3. Where are we importing to?

4. "... the worksheets will be Random and I will not know the names" Which worksheets (importing from? importing to?)? And how will we decide what worksheets there are and which ones to use?
 
Upvote 0
Ok, I've thought about it. I was thinking of using something similar to this piece of code.


Code: 
Private Sub cmdImport_Click()
    Dim intNumber As Integer
    Dim intCount As Integer
    Dim wkbBook As Workbook
    Dim varFile As Variant
    Dim strSheet(5) As String
    strSheet(0) = "Output"
    strSheet(1) = "Catoutput"
    strSheet(2) = "PCSOutput"
    strSheet(3) = "Saloutput"
    strSheet(4) = "invOutput"
    strSheet(5) = "Address"
    On Error GoTo Fin
    varFile = Application.GetOpenFileName("All files,*.xls", 1, "Select", , True)
    If Not IsArray(varFile) Then Exit Sub
    Application.ScreenUpdating = False
    For intCount = 1 To UBound(varFile)
        Set wkbBook = Workbooks.Open(varFile(intCount))
        For intNumber = 0 To 4
            If WorkSheetExists(wkbBook.Name, strSheet(intNumber)) Then
                If WorkSheetExists(ThisWorkbook.Name, strSheet(intNumber)) Then
                    Application.DisplayAlerts = False
                    ThisWorkbook.Worksheets(strSheet(intNumber)).Delete
                    Application.DisplayAlerts = True
                    wkbBook.Sheets(strSheet(intNumber)).Copy _
                        Before:=ThisWorkbook.Worksheets(1)
                Else
                    wkbBook.Sheets(strSheet(intNumber)).Copy _
                        Before:=ThisWorkbook.Worksheets(1)
                End If
            End If
        Next intNumber
        wkbBook.Close False
    Next intCount
Fin:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub


1. The rows would look like the following which starts off at Row11 of a Random Worksheet.

2. Exporting from another Workbook and the Workbook and Worksheet name would be random.

3.This would be imported to a sheet called Saloutput.

4. I Guess I could use an Input Box to Allow the User to Specify the Worksheet name?

I'd Also like to Filter out the 0 Entries and I would expect there to be no more than 100 rows.

This would be imported to a sheet called Saloutput.

<TABLE style="WIDTH: 268pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=357 border=0 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 5248" width=123><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 9984" width=234><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=123 height=17>Salary_CatIDCode</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 176pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=234>Sum_Salary</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACTLBSCSLR</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="542097755.55999994">542097755.6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ANNLBSCSLR</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="11082903557.110001">11082903557</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BASCFLTMEQ</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="5162989895.46">5162989895</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BASSAL</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="5089761428.4700003">5089761428</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>BONUSPAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="96638810.629999995">96638810.63</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CHLDCVCHRS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>2221502.45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CONTSALARY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CSGROSSAL</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FLCTTMLM01</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MDDLBNDERN</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>MTRNTPAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="31807136.329999998">31807136.33</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>P11DRNNG01</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>P60EARNNGS</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="5915034055.71">5915034056</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PNSNBSALRY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SHIFTPAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="194396094.53999999">194396094.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>SICKPAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num>4359374.31</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Transfer data but not rows with a certain blank cell
Replies
2
Views
251
UTB
U
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
488
MissDB101
M
D
  • Question
VBA help to create button that moves to next row after taking screenshot
Replies
1
Views
105
ranman256
ranman256
K
  • Question
Copy selected rows from txt file and paste to excel based on one criteria applied on one column
Replies
5
Views
138
dmt32
D
E
  • Question
Setting multiple row heights in VBA
Replies
2
Views
175
Extivalis
E

Forum statistics

Threads
1,214,912
Messages
6,122,200
Members
449,072
Latest member
DW Draft

Share this page

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
Back
Top