• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data to separate sheet depending on value of cell

K

Kellan Adamson

New Member
Hey there folks!

I have one that I am a bit stuck on the best way to do it.

I have a bunch of data that I'm importing from SQL. I want to separate it into 4 different sheets depending on what number from 1-4 is in a column. Then I want to take all the data from that row and put it into the appropriate sheet. Rinse and repeat down the entire sheet.

What is the best way to do that?

Basically:
if F1=1 copy all the data from A1:V1 to Sheet 1
if F1=2 copy all the data from A1:V1 to Sheet 2
 
VBA would be quickest, you can probably use pivots too just include all the data, copy paste and filter for the one value.

Below is not my work, I added this to my ribbon a few years back... This takes any unique values from column A, organizes them into separate sheets.
So it will split your 1s and 2s ad 3s and 4s to separate sheets. Quite handy. Just move your number column to be column A. And do not run this if A has a hundred unique values, it will go nuts.


Code: 
Sub Parse_Data()

Sub Parse_Data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub

Enjoy! And welcome to the forums :)
 
Thanks for your quick reply!!!

So since I suck at VBA could you explain a couple things?

1. How do I change the row where it looks for the values? I want to change this to column R
2. How do I add multiple worksheets into this? I need to do 1-4 and have the first sheet as the import data and then 2-5 as the individual sheets: 1 to Installs, 2 to Service Vehicles, 3 to Employee Vehicles, 4 to Shop Vehicles.
3. Is there any way to select specific rows to copy? So if I wanted to skip copying the data from rows F and G, how can I build that into the code?

My apologies for my ignorance. I hate VBA and every time I try to wrap my hear around it I get even more confused than when I started. Thanks again for your help.
 
Kellan Adamson said:
Thanks for your quick reply!!!

So since I suck at VBA could you explain a couple things?

1. How do I change the row where it looks for the values? I want to change this to column R
2. How do I add multiple worksheets into this? I need to do 1-4 and have the first sheet as the import data and then 2-5 as the individual sheets: 1 to Installs, 2 to Service Vehicles, 3 to Employee Vehicles, 4 to Shop Vehicles.
3. Is there any way to select specific rows to copy? So if I wanted to skip copying the data from rows F and G, how can I build that into the code?

My apologies for my ignorance. I hate VBA and every time I try to wrap my hear around it I get even more confused than when I started. Thanks again for your help.
Click to expand...
I can try, but first re. your point 2, I suspect your tabs 'Installs' etc. already have other data in them, and you want to keep that data and paste the new data below, huh?
 
That's exactly it. We pull a bunch of data from our fuel pump tracking system on a weekly basis and then need to spit it up and do some magic to it before accounting get their hands on it.

Since we are just starting out, no there is no data in any of the tabs but once they are populated, I would like the data to not be overwritten.
 
Sorry it's a little late, life called. Take a look at the attached I think I addressed all your concerns. Look into the code, 2 areas to bring to your attention:

ws.Columns("F:F").EntireColumn.Hidden = True

You can edit that row of code for whatever columns you want not to carry across, just change the letters quoted. Copy and paste it to hide more columns.

And

'Sheets(myarr(i) & "").Columns.AutoFit

a ' in front of text makes this a comment and therefore currently inactive, if you remove the ' it will become a line of code. It resizes the columns (in new sheets you just created) to fit the dataset. I personally find it annoying but the option is there for you to activate it.

I attached it to a button there, that probably makes sense, or add it to your ribbon or as a keyboard shortcut, whatever works. If you want the button on another sheet, just add a line to the very front of the code ,
just below the Sub Parse_Data(), add:
Sheets("Import").Activate

The name in brackets being the name of the sheet that has data to be imported.
 

Attachments

  • Parse.xlsm
    23.1 KB · Views: 9
Last edited:
Just a little note, I believe the code currently assumes that whatever data is in column A is filled (no blank values), if your extracts might feature blank values in column A let me know I can review it.
 
Kellan Adamson said:
I have a bunch of data that I'm importing from SQL. I want to separate it into 4 different sheets depending on what number from 1-4 is in a column.
<snip>
What is the best way to do that?
Click to expand...
Your're bringing in the data with SQL? Surely 4 separate SQL statements to each sheet would be the tidiest way?

Another way, which should be quite quick as here are only 4 sheets to add to, is to Autofilter the source sheet, filter for the 1s, 2s, 3s etc., and simply select the filtered results, copy and paste to the bottom of the appropriate destination sheet's existing table.
 
Last edited:
Nightlytic said:
Just a little note, I believe the code currently assumes that whatever data is in column A is filled (no blank values), if your extracts might feature blank values in column A let me know I can review it.
Click to expand...


It all works perfectly! THANK YOU SO MUCH!!!

The only thing I'm trying to figure out now is how to only import new items. Column B is the transaction # so that will always be a sequential number so I think I should use that. Can you use Excel cell values in SQL queries? My thought: Use LOOKUP on column B to find the value of the last cell and then use that value in the SQL query to only import items that are greater than the value in that cell.

I'm trying =LOOKUP(2,1/(1-ISBLANK(B:B)),B:B) to get the last cell value but that doesn't seem to give me what I want. It's giving me 0 when it should be 356. Same with =INDEX(B:B, COUNTA(B:B), 1). I don't know why I'm not getting the last cell value in the column.
 
Last edited:
If your references are always numbers, increasing in a sequence, then a simple =Max(B:B) will pull the largest number. Which should also be the last.
 
Idk I don't have your SQL query =p And I admit I don't know SQL either :(
We can work it into the macro, possibly, if you want to do that.
 
Nightlytic said:
Idk I don't have your SQL query =p And I admit I don't know SQL either :(
We can work it into the macro, possibly, if you want to do that.
Click to expand...

This is what I have in my macro. If I remove the WHERE statement it pulls everything without issue.

StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE sequencenumber>(CELL THAT CONTAINS =MAX(B:B) - currently AA1)
 
Nightlytic said:
Idk I don't have your SQL query =p And I admit I don't know SQL either :(
We can work it into the macro, possibly, if you want to do that.
Click to expand...

I got the SQL part figured out and I thought I had it done but hit one more issue.

So I have the SQL query only pull the newest info to the IMPORT sheet each pull but when I run the sort and parse part of the code it just re-adds the data after the last run to each of the numbered sheets. Is there any way I can have it only pull the newest entries?

If I use the value of AA1 on the IMPORT sheet as the newest entry number, can the parse just pull everything greater than that number?

BTW if you are interested, this is how I got the SQL part working with some help from other folks on here.

Code: 
Sub Download_and_Parse()

Sheets("Import").Activate
Range("AA1").Select
ActiveCell.FormulaR1C1 = "=MAX(C[-25])"

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
  'Make sure to change:
      '1: PASSWORD
      '2: USERNAME
      '3: REMOTE_IP_ADDRESS
      '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=WINFUEL"

'Opens connection to the database
    cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT * FROM [ASI].[ExportView] WHERE SequenceNumber > " & Sheets("Import").Range("AA1").Value
    Debug.Print StrQuery
'Performs the actual query
    rst.Open StrQuery, cnn
   
'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
 
You must log in or register to reply here.
Back
Top