copy data to different sheets

Question

hello again everybody!
I have this workbook I am working at and all sheets are named with a 2 letters code (i.e. HK, JD etc). The sheet that contains the data (called "All Flights IN" has headers which are the same for all sheets (the problem here is that not all sheets have the same amount of columns: i.e "All Flights IN" has 5 columns, Sheet1 has 3, Sheet2 has 4 - but if a sheet has a specific column this will have the exact name as in "All Flights IN"). The first column in "All Flights IN" has the 2 letter codes and the data should be distributed among the sheets based on that (and it should go to the first available empty row). Is this possible?

thanks for your help

Answer 1

Hi s7yzrs,

i think i am following you

this is the all flight in sheet data

colheader1     colheader2     etc... up to 5 columns

HK                 HKdata       more data
JD                 JDdata        more data
etc...             etc...           etc...


(in the future it would help if you can give an example like above so we can follow better.)  but you explained it fairly well i think.


so the first column has the sheet names and the remaining columns have data for each sheet

you want to go down the list in column1 and based on the sheet name in the cell you want to take the corresponding data in the other cells of that row and place it at the end of the sheet listed in the first cell?

so from above, the first row is for sheet HK and the data from colheader2 up to colheader5 will be placed at the end of the HK worksheet.  same for JD on the next row, etc...




here is an example you can try

Sub populate_sheet_based_on_column_values()

    Dim lastrow As Integer: lastrow = Sheet1.UsedRange.Row + 1
    Dim col1cell As Range
    
    Dim codesheetlastrow As Integer
    Dim currrentworkingsheet As String
    
    For Each col1cell In Sheet1.Range("A2:A" + CStr(lastrow))
        
        Dim sheetname As String: sheetname = col1cell.Value
        Dim codesheet As Worksheet: Set codesheet = Worksheets(sheetname)
        
            Dim coderow As Integer: coderow = col1cell.Row
        Dim coderowrng As Range
        Set coderowrng = Sheet1.Range("B" + CStr(coderow) + ":E" + CStr(coderow))
        
        If Not currentworkingsheet = codesheet.Name Then
            currentworkingsheet = codesheet.Name
            codesheetlastrow = codesheet.UsedRange.Rows.Count + 1
       End If
       
        codesheetlastrow = codesheetlastrow + 1
        
        Dim codesheetlastrng As Range
        Set codesheetlastrng = codesheet.Range("A" + CStr(codesheetlastrow) + ":D" + CStr(codesheetlastrow))
        
        codesheetlastrng.Value = coderowrng.Value
        
    Next

End Sub

how it works:

the first line of code after the sub declaration get the count of the All Flight In sheet (right not it is just sheet1.  if you have all flight in as the first worksheet then you can leave this otherwise you can replace the 1 after sheet with whatever the number order your sheet falls in.  or you can replace sheet1 with worksheets("All Flight In").    that will get the worksheet by name.  you would also have to do the same as above for the for each col1cell line for sheet1 again.

the loop goes through each rows in the worksheet (Sheet1 or All Flight In)
Sheet1.Range("A2:A" + CStr(lastrow))  this defines the range it will go through starting with A2 down to the last row populated with data.  this is all based on column 1 or (A).

as it goes through each row it gets the name of each sheet it needs to populate, from the first column. since it works with column A, col1cell in the for each is each cell in column A starting from row 2 down to the last row in column A with data in it.

it gets the sheet name from the first cell (col1cell)
then creates a new worksheet object using the worksheet name it needs to populate.  (this is just a variable object not an actual sheet).  no additional sheets are created.  this is just for code use.

then gets the current row an creates a range object for cells B to E.  this is for the actual data that goes in the corresponding sheet to col1cell.  (HK, JD, etc...)

there is a check in there to see if we get to a new worksheet name in the loop so we can get the last row count for each sheet.
i did it this way because usedrows should update automatically after adding data to a sheet but it seems to be a little "buggy".  it was not consistent.
so i store the count in order to use it in the codesheetlastrow variable to get the correct new row for each sheet.

the gets a range object for the last row in the code sheet to populate (HK, JD, etc...)

and finally set the value of the current range data in the all flight in sheet to the corresponding code sheet last row range.  (the next empty row)



the only thing you will have to consider is the use of the usedrange for each sheet will only work once.  i assume you have each code sheet listed 1 time in the all flight in sheet.  meaning, 1 row of data per code sheet in all flgiht in rows.  if you have each code sheet listed more than once and specifically out of order then you may have an issue with the usedrange count for each sheet and it may just populate over the last row over and over.

if this happens then you may need to store the code sheet last row populated either in a collection of some sort or have variables for each code sheet if there are not that many.  that way you can increment the last row you populate for each sheet after you add the new row data to it.

if you do have that issue and you need help with it, tell me more about how many sheets you have to populate and their names if not too many.  if you have a ton of sheets then just tell me how many and i can help you come up with a way to store the count.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 2

Jeff, first of all thank you for the answer.

Where do I have to input the code? I linked it to a command button but nothing happened.

I have this idea, tell  me what you think about it: I have this userform which is used to input the data to "All Flights IN". But now I think that we could try and lose that sheet and get the data directly from the userform to the various sheets (All Flights IN has been created only to gather and distribute all info). The only comboBox in the userform delivers the 2 letter codes used to name the sheets too... would this be handier/easier? Only certain data goes to the sheets, though. And here comes catch 22: the first column should "concatenate" combobox, textbox1 and textbox2 (which is the date, and only the day digits are required), texbox4, textbox5, textbox6.

That's just about it. I am not sure I explained myself clearly, It's been a rough day at the airport today.

Thanks for your help

Answer 3

why it didn't work i can't say without seeing the code and knowing more about the workbook

but aside from that, it would make sense if you can bypass the all flights in sheet and just populate the correct sheets directly.

concatenating data is not a big deal. 
you can do this
something = combobox1.text + textbox1.text + textbox2.text

if you need to get just part of a date, you can use the string functions like (mid) or there are methods to work with dates.  i don't know the code off hand but you can do a search for vba date functions.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 4

Hi again,

here are the codes I have under the button I would like to use to do the job.

Private Sub Finito_Click()

'mette i dati in "Veline"

Sheets("Veline").Cells(6, 8).Value = UCase(ComboAirline.Text)
Sheets("Veline").Cells(6, 12).Value = UCase(Fnr.Text)
Sheets("Veline").Cells(6, 26).Value = UCase(Data.Text)
Sheets("Veline").Cells(11, 14).Value = UCase(Pieces.Text)
Sheets("Veline").Cells(13, 14).Value = UCase(GWeight.Text)
Sheets("Veline").Cells(15, 14).Value = UCase(CWeight.Text)
Sheets("Veline").Cells(17, 14).Value = UCase(TotAwb.Text)
Sheets("Veline").Cells(19, 14).Value = UCase(Comail.Text)
Sheets("Veline").Cells(21, 14).Value = UCase(Post.Text)
Sheets("Veline").Cells(23, 14).Value = (Prepa.Text)
Sheets("Veline").Cells(15, 21).Value = (Remarks.Text)

'Stampa "Veline"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'Copia i dati in "All Flights IN"
Dim lrw As Long
Dim col As Integer

col = 1 ' Column A
lrw = Sheets("All Flights IN").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row
'If lrw > 31 Then ' Column B is full
'    col = 16      ' Set to column P
    lrw = Sheets("All Flights IN").Cells(Rows.Count, col).End(xlUp).Offset(1, 0).Row
'End If

If lrw < 2 Then
    lrw = 2
End If

Sheets("All Flights IN").Cells(lrw, col) = ComboAirline.Text
Sheets("All Flights IN").Cells(lrw, col + 1) = Fnr.Text
Sheets("All Flights IN").Cells(lrw, col + 2) = Data.Value
Sheets("All Flights IN").Cells(lrw, col + 3) = GWeight.Value
Sheets("All Flights IN").Cells(lrw, col + 4) = CWeight.Value
Sheets("All Flights IN").Cells(lrw, col + 5) = TotAwb.Value
'Sheets("All Flights IN").Cells(lrw, col + 3) = GWeight.Value

Sheets("All Flights IN").Select
Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Range("A2").Select
    Sheets("Veline").Select

Sub
 populate_sheet_based_on_column_values()



    Dim
 lastrow As
 Integer
: lastrow = Sheet2.UsedRange.Row + 1

    Dim
 col1cell As
 Range

    

    Dim
 codesheetlastrow As
 Integer


    Dim
 currrentworkingsheet As
 String


    

    For
 Each
 col1cell In
 Sheet2.Range("A2:A"
 + CStr
(lastrow))

        

        Dim
 sheetname As
 String
: sheetname = col1cell.Value

        Dim
 codesheet As
 Worksheet: Set
 codesheet = Worksheets(sheetname)

        

            Dim
 coderow As
 Integer
: coderow = col1cell.Row

        Dim
 coderowrng As
 Range

        Set
 coderowrng = Sheet2.Range("B"
 + CStr
(coderow) + ":E"
 + CStr
(coderow))

        

        If
 Not
 currentworkingsheet = codesheet.Name Then


            currentworkingsheet = codesheet.Name

            codesheetlastrow = codesheet.UsedRange.Rows.Count
 + 1

       End
 If


       

        codesheetlastrow = codesheetlastrow + 1

        

        Dim
 codesheetlastrng As
 Range

        Set
 codesheetlastrng = codesheet.Range("A"
 + CStr
(codesheetlastrow) + ":D"
 + CStr
(codesheetlastrow))

        

        codesheetlastrng.Value = coderowrng.Value

        

    Next




End
 Sub

'Svuota Userform

'ComboAirline.Value = ""
'Fnr.Value = ""
'Data.Value = ""
'GWeight.Value = ""
'CWeight.Value = ""
'TotAwb.Value = ""
'Comail.Value = ""
'Post.Value = ""
'Prepa.Value = ""
'Remarks.Value = ""

'Svuota "Veline"

Sheets("Veline").Cells(6, 8).Value = ""
Sheets("Veline").Cells(6, 12).Value = ""
Sheets("Veline").Cells(6, 26).Value = ""
Sheets("Veline").Cells(11, 14).Value = ""
Sheets("Veline").Cells(13, 14).Value = ""
Sheets("Veline").Cells(15, 14).Value = ""
Sheets("Veline").Cells(17, 14).Value = ""
Sheets("Veline").Cells(19, 14).Value = ""
Sheets("Veline").Cells(21, 14).Value = ""
Sheets("Veline").Cells(23, 14).Value = ""
Sheets("Veline").Cells(15, 21).Value = ""
'Selection.ClearContents



'chiude userform

Unload Me

'Salva
ActiveWorkbook.Save

'Chiude tutto


'Application.quit

End Sub

The code you suggested is the only one in colour.

How would you go about to try and get the right data in the right sheet without using "All Flights IN"?

Answer 5

took me a while to get back to you.  i don't get alerts for all posts for some reason.  not sure why.

i have also been busy trying to track down some issues on my website.  after making 2 weeks worth of changes, they don't work correctly after uploading to my web host.  oh the joys of developing.


is the project you have something you could send to me?  it would be much easier if i could see what you have rather than try to guess at how you have everything.

if you want to do that send it to    jeff @ txun . net      the forum removes email links so just remove the spaces.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 6

I'm just posting what i sent you already by email just in case anyone else is trying to do something similar.  may help some others.



i created a class module called  CLS_Worksheets

the class contains two functions.  1 to find if a worksheet exists and 1 to create a new worksheet with a new name

Public Function WorksheetExists(SheetName As String)
    Dim foundsheet As Boolean
    foundsheet = False
    
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
        If sht.Name = SheetName Then
        foundsheet = True
        End If
    Next
    
    If foundsheet = True Then
        WorksheetExists = True
    Else
        WorksheetExists = False
    End If
    
End Function


Public Function CreateNewSheet(SheetName As String)
    Dim createdsheet As Boolean
    createdsheet = False
    
    ThisWorkbook.Worksheets.Add
    'find the new sheet
    For Each existingsht In ThisWorkbook.Sheets
        'Dim start As String: start = Left(existingsht.Name, 5)
        If Left(existingsht.Name, 5) = "Sheet" Then
            existingsht.Name = SheetName
            createdsheet = True
            Exit For
        End If
    Next
    
    If createdsheet = True Then
        CreateNewSheet = True
    Else
        CreateNewSheet = False
    End If

End Function
<br/><br/><br/><br/><br/><br/>

here is the userform code to take a selection from a combobox and find if a sheet with that selected name already exists.  if not a sheet with that name is created.  and finally some data is populated to the next blank row in the sheet

Option Explicit

Private Sub cmdNext_Click()

    'this gets the worksheet using the name selected in the comboairline
    Dim selectedcode As String
    selectedcode = Me.ComboAirline.Text
    
    
    'find the airline code selected and see if a sheet exists for it yet
    Dim shts As New CLS_Worksheets
    If shts.WorksheetExists(selectedcode) = False Then
        'selected sheet does not exist so create it
        If shts.CreateNewSheet(selectedcode) = True Then
        'if we created a new sheet, it can be populated with the headers
        Sheets(selectedcode).Range("A1").Value = "Flight"
        End If
    End If


    
    'now that we have either found or created the new sheet we can get it and populate it
    Dim codesht As Worksheet: Set codesht = Sheets(selectedcode)
    
    
    
    'this block below just gets the next empty row in the sheet selected
    Dim nextrow As Integer
    Dim rng As Range
    For Each rng In codesht.Range("A:A")
        If rng.Value = "" Then
            nextrow = rng.Row
            Exit For
        End If
    Next


    'now that we have the correct sheet and the next empty row we
    'can concat the 3 items and add the new row
    'ComboAirline, Fnr and Data to concat
    'OZ780/11
    
    'next two line are just for testing
    Me.Fnr.Text = "280"
    Me.Data.Text = Format(Now, "mm/dd/yyyy")
    
    
    Dim flight As String
    flight = ComboAirline.Text
    flight = flight + Fnr.Text
    Dim fdate As String
    'get just the day from the date
    fdate = Format(Me.Data.Text, "dd")
    'remove the 0 in the date.  can be commented out if need the 0
    fdate = Replace(fdate, "0", "")
    
    flight = flight + "/" + fdate
    
       
    'add the new row
    codesht.Range("A" + CStr(nextrow)).Value = flight
    
End Sub


hope this helps someone else as well
 

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial