Hi all,
I'm looking for a way to speed up my database data transfers. I have a Sheet which holds all my data and I transfer it to other sheets as needed. There is an example below.
Any ideas on how to optimize this and make it better? Like loop it or something? idk... Thanks for any tips or ideas in advance!
I'm looking for a way to speed up my database data transfers. I have a Sheet which holds all my data and I transfer it to other sheets as needed. There is an example below.
Any ideas on how to optimize this and make it better? Like loop it or something? idk... Thanks for any tips or ideas in advance!
Code:
Private Sub CommandButton7_Click()
Dim LastRow As Long, ws As Worksheet, wss As Worksheet
Set ws = Sheets("DataBase")
Set wss = Sheets("Sheet1")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
For Row = 2 To LastRow
If TextBox6.Text = ws.Cells(Row, 1).Value Then
ws.Range("B" & Row).Value = ComboBox1.Text
ws.Range("C" & Row).Value = ComboBox2.Text
ws.Range("D" & Row).Value = TextBox1.Text
ws.Range("E" & Row).Value = ComboBox3.Text
ws.Range("F" & Row).Value = ComboBox4.Text
ws.Range("G" & Row).Value = ComboBox5.Text
ws.Range("H" & Row).Value = ComboBox6.Text
ws.Range("I" & Row).Value = TextBox2.Text
ws.Range("T" & Row).Value = ComboBox7.Text
ws.Range("U" & Row).Value = Cells(41, "D")
ws.Range("V" & Row).Value = Cells(41, "F")
ws.Range("W" & Row).Value = Cells(41, "P")
ws.Range("X" & Row).Value = Cells(41, "AO")
ws.Range("Y" & Row).Value = Cells(41, "AS")
ws.Range("Z" & Row).Value = Cells(41, "AW")
ws.Range("AA" & Row).Value = Cells(41, "BA")
ws.Range("AB" & Row).Value = Cells(41, "BF")
ws.Range("AC" & Row).Value = Cells(42, "D")
ws.Range("AD" & Row).Value = Cells(42, "F")
ws.Range("AE" & Row).Value = Cells(42, "P")
ws.Range("AF" & Row).Value = Cells(42, "AO")
ws.Range("AG" & Row).Value = Cells(42, "AS")
ws.Range("AH" & Row).Value = Cells(42, "AW")
ws.Range("AI" & Row).Value = Cells(42, "BA")
ws.Range("AJ" & Row).Value = Cells(42, "BF")
ws.Range("AK" & Row).Value = Cells(43, "D")
ws.Range("AL" & Row).Value = Cells(43, "F")
ws.Range("AM" & Row).Value = Cells(43, "P")
ws.Range("AN" & Row).Value = Cells(43, "AO")
ws.Range("AO" & Row).Value = Cells(43, "AS")
ws.Range("AP" & Row).Value = Cells(43, "AW")
ws.Range("AQ" & Row).Value = Cells(43, "BA")
ws.Range("AR" & Row).Value = Cells(43, "BF")
ws.Range("AS" & Row).Value = Cells(44, "D")
ws.Range("AT" & Row).Value = Cells(44, "F")
ws.Range("AU" & Row).Value = Cells(44, "P")
ws.Range("AV" & Row).Value = Cells(44, "AO")
ws.Range("AW" & Row).Value = Cells(44, "AS")
ws.Range("AX" & Row).Value = Cells(44, "AW")
ws.Range("AY" & Row).Value = Cells(44, "BA")
ws.Range("AZ" & Row).Value = Cells(44, "BF")
ws.Range("BA" & Row).Value = Cells(45, "D")
ws.Range("BB" & Row).Value = Cells(45, "F")
ws.Range("BC" & Row).Value = Cells(45, "P")
ws.Range("BD" & Row).Value = Cells(45, "AO")
ws.Range("BE" & Row).Value = Cells(45, "AS")
ws.Range("BF" & Row).Value = Cells(45, "AW")
ws.Range("BG" & Row).Value = Cells(45, "BA")
ws.Range("BH" & Row).Value = Cells(45, "BF")
ws.Range("BI" & Row).Value = Cells(46, "D")
ws.Range("BJ" & Row).Value = Cells(46, "F")
ws.Range("BK" & Row).Value = Cells(46, "P")
ws.Range("BL" & Row).Value = Cells(46, "AO")
ws.Range("BM" & Row).Value = Cells(46, "AS")
ws.Range("BN" & Row).Value = Cells(46, "AW")
ws.Range("BO" & Row).Value = Cells(46, "BA")
ws.Range("BP" & Row).Value = Cells(46, "BF")
ws.Range("BQ" & Row).Value = Cells(47, "D")
ws.Range("BR" & Row).Value = Cells(47, "F")
ws.Range("BS" & Row).Value = Cells(47, "P")
ws.Range("BT" & Row).Value = Cells(47, "AO")
ws.Range("BU" & Row).Value = Cells(47, "AS")
ws.Range("BV" & Row).Value = Cells(47, "AW")
ws.Range("BW" & Row).Value = Cells(47, "BA")
ws.Range("BX" & Row).Value = Cells(47, "BF")
ws.Range("BY" & Row).Value = Cells(48, "D")
ws.Range("BZ" & Row).Value = Cells(48, "F")
ws.Range("CA" & Row).Value = Cells(48, "P")
ws.Range("CB" & Row).Value = Cells(48, "AO")
ws.Range("CC" & Row).Value = Cells(48, "AS")
ws.Range("CD" & Row).Value = Cells(48, "AW")
ws.Range("CE" & Row).Value = Cells(48, "BA")
ws.Range("CF" & Row).Value = Cells(48, "BF")
ws.Range("CG" & Row).Value = Cells(49, "D")
ws.Range("CH" & Row).Value = Cells(49, "F")
ws.Range("CI" & Row).Value = Cells(49, "P")
ws.Range("CJ" & Row).Value = Cells(49, "AO")
ws.Range("CK" & Row).Value = Cells(49, "AS")
ws.Range("CL" & Row).Value = Cells(49, "AW")
ws.Range("CM" & Row).Value = Cells(49, "BA")
ws.Range("CN" & Row).Value = Cells(49, "BF")
ws.Range("CO" & Row).Value = Cells(50, "D")
ws.Range("CP" & Row).Value = Cells(50, "F")
ws.Range("CQ" & Row).Value = Cells(50, "P")
ws.Range("CR" & Row).Value = Cells(50, "AO")
ws.Range("CS" & Row).Value = Cells(50, "AS")
ws.Range("CT" & Row).Value = Cells(50, "AW")
ws.Range("CU" & Row).Value = Cells(50, "BA")
ws.Range("CV" & Row).Value = Cells(50, "BF")
ws.Range("CW" & Row).Value = Cells(51, "D")
ws.Range("CX" & Row).Value = Cells(51, "F")
ws.Range("CY" & Row).Value = Cells(51, "P")
ws.Range("CZ" & Row).Value = Cells(51, "AO")
ws.Range("DA" & Row).Value = Cells(51, "AS")
ws.Range("DB" & Row).Value = Cells(51, "AW")
ws.Range("DC" & Row).Value = Cells(51, "BA")
ws.Range("DD" & Row).Value = Cells(51, "BF")
ws.Range("DE" & Row).Value = Cells(52, "D")
ws.Range("DF" & Row).Value = Cells(52, "F")
ws.Range("DG" & Row).Value = Cells(52, "P")
ws.Range("DH" & Row).Value = Cells(52, "AO")
ws.Range("DI" & Row).Value = Cells(52, "AS")
ws.Range("DJ" & Row).Value = Cells(52, "AW")
ws.Range("DK" & Row).Value = Cells(52, "BA")
ws.Range("DL" & Row).Value = Cells(52, "BF")
ws.Range("DM" & Row).Value = Cells(53, "D")
ws.Range("DN" & Row).Value = Cells(53, "F")
ws.Range("DO" & Row).Value = Cells(53, "P")
ws.Range("DP" & Row).Value = Cells(53, "AO")
ws.Range("DQ" & Row).Value = Cells(53, "AS")
ws.Range("DR" & Row).Value = Cells(53, "AW")
ws.Range("DS" & Row).Value = Cells(53, "BA")
ws.Range("DT" & Row).Value = Cells(53, "BF")
ws.Range("DU" & Row).Value = Cells(54, "D")
ws.Range("DV" & Row).Value = Cells(54, "F")
ws.Range("DW" & Row).Value = Cells(54, "P")
ws.Range("DX" & Row).Value = Cells(54, "AO")
ws.Range("DY" & Row).Value = Cells(54, "AS")
ws.Range("DZ" & Row).Value = Cells(54, "AW")
ws.Range("FA" & Row).Value = Cells(54, "BA")
ws.Range("FB" & Row).Value = Cells(54, "BF")
ws.Range("FC" & Row).Value = Cells(55, "D")
ws.Range("FD" & Row).Value = Cells(55, "F")
ws.Range("GJ" & Row).Value = Cells(55, "P")
ws.Range("GK" & Row).Value = Cells(55, "AO")
ws.Range("GL" & Row).Value = Cells(55, "AS")
ws.Range("GM" & Row).Value = Cells(55, "AW")
ws.Range("GN" & Row).Value = Cells(55, "BA")
ws.Range("GO" & Row).Value = Cells(55, "BF")
ws.Range("GP" & Row).Value = Cells(56, "D")
ws.Range("GQ" & Row).Value = Cells(56, "F")
ws.Range("GR" & Row).Value = Cells(56, "P")
ws.Range("GS" & Row).Value = Cells(56, "AO")
ws.Range("GT" & Row).Value = Cells(56, "AS")
ws.Range("GU" & Row).Value = Cells(56, "AW")
ws.Range("GV" & Row).Value = Cells(56, "BA")
ws.Range("GW" & Row).Value = Cells(56, "BF")
ws.Range("GX" & Row).Value = Cells(57, "D")
ws.Range("GY" & Row).Value = Cells(57, "F")
ws.Range("GZ" & Row).Value = Cells(57, "P")
ws.Range("HA" & Row).Value = Cells(57, "AO")
ws.Range("HB" & Row).Value = Cells(57, "AS")
ws.Range("HC" & Row).Value = Cells(57, "AW")
ws.Range("HD" & Row).Value = Cells(57, "BA")
ws.Range("HE" & Row).Value = Cells(57, "BF")
ws.Range("HF" & Row).Value = Cells(58, "D")
ws.Range("HG" & Row).Value = Cells(58, "F")
ws.Range("HH" & Row).Value = Cells(58, "P")
ws.Range("HI" & Row).Value = Cells(58, "AO")
ws.Range("HJ" & Row).Value = Cells(58, "AS")
ws.Range("HK" & Row).Value = Cells(58, "AW")
ws.Range("HL" & Row).Value = Cells(58, "BA")
ws.Range("HM" & Row).Value = Cells(58, "BF")
ws.Range("HN" & Row).Value = Cells(59, "D")
ws.Range("HO" & Row).Value = Cells(59, "F")
ws.Range("HP" & Row).Value = Cells(59, "P")
ws.Range("HQ" & Row).Value = Cells(59, "AO")
ws.Range("HR" & Row).Value = Cells(59, "AS")
ws.Range("HS" & Row).Value = Cells(59, "AW")
ws.Range("HT" & Row).Value = Cells(59, "BA")
ws.Range("HU" & Row).Value = Cells(59, "BF")
ws.Range("HV" & Row).Value = Cells(60, "D")
ws.Range("HW" & Row).Value = Cells(60, "F")
ws.Range("HX" & Row).Value = Cells(60, "P")
ws.Range("HY" & Row).Value = Cells(60, "AO")
ws.Range("HZ" & Row).Value = Cells(60, "AS")
ws.Range("IA" & Row).Value = Cells(60, "AW")
ws.Range("IB" & Row).Value = Cells(60, "BA")
ws.Range("IC" & Row).Value = Cells(60, "BF")
ws.Range("ID" & Row).Value = Cells(61, "D")
ws.Range("IE" & Row).Value = Cells(61, "F")
ws.Range("IF" & Row).Value = Cells(61, "P")
ws.Range("IG" & Row).Value = Cells(61, "AO")
ws.Range("IH" & Row).Value = Cells(61, "AS")
ws.Range("II" & Row).Value = Cells(61, "AW")
ws.Range("IJ" & Row).Value = Cells(61, "BA")
ws.Range("IK" & Row).Value = Cells(61, "BF")
ws.Range("IL" & Row).Value = Cells(62, "D")
ws.Range("IM" & Row).Value = Cells(62, "F")
ws.Range("IN" & Row).Value = Cells(62, "P")
ws.Range("IO" & Row).Value = Cells(62, "AO")
ws.Range("IP" & Row).Value = Cells(62, "AS")
ws.Range("IQ" & Row).Value = Cells(62, "AW")
ws.Range("IR" & Row).Value = Cells(62, "BA")
ws.Range("IS" & Row).Value = Cells(62, "BF")
ws.Range("IT" & Row).Value = Cells(63, "D")
ws.Range("IU" & Row).Value = Cells(63, "F")
ws.Range("IV" & Row).Value = Cells(63, "P")
ws.Range("IW" & Row).Value = Cells(63, "AO")
ws.Range("IX" & Row).Value = Cells(63, "AS")
ws.Range("IY" & Row).Value = Cells(63, "AW")
ws.Range("IZ" & Row).Value = Cells(63, "BA")
ws.Range("JA" & Row).Value = Cells(63, "BF")
ws.Range("JB" & Row).Value = Cells(64, "D")
ws.Range("JC" & Row).Value = Cells(64, "F")
ws.Range("JD" & Row).Value = Cells(64, "P")
ws.Range("JE" & Row).Value = Cells(64, "AO")
ws.Range("JF" & Row).Value = Cells(64, "AS")
ws.Range("JG" & Row).Value = Cells(64, "AW")
ws.Range("JH" & Row).Value = Cells(64, "BA")
ws.Range("JI" & Row).Value = Cells(64, "BF")
ws.Range("JJ" & Row).Value = Cells(65, "D")
ws.Range("JK" & Row).Value = Cells(65, "F")
ws.Range("JL" & Row).Value = Cells(65, "P")
ws.Range("JM" & Row).Value = Cells(65, "AO")
ws.Range("JN" & Row).Value = Cells(65, "AS")
ws.Range("JO" & Row).Value = Cells(65, "AW")
ws.Range("JP" & Row).Value = Cells(65, "BA")
ws.Range("JQ" & Row).Value = Cells(65, "BF")
ws.Range("JR" & Row).Value = Cells(66, "D")
ws.Range("JS" & Row).Value = Cells(66, "F")
ws.Range("JT" & Row).Value = Cells(66, "P")
ws.Range("JU" & Row).Value = Cells(66, "AO")
ws.Range("JV" & Row).Value = Cells(66, "AS")
ws.Range("JW" & Row).Value = Cells(66, "AW")
ws.Range("JX" & Row).Value = Cells(66, "BA")
ws.Range("JY" & Row).Value = Cells(66, "BF")
ws.Range("JZ" & Row).Value = Cells(67, "D")
ws.Range("KA" & Row).Value = Cells(67, "F")
ws.Range("KB" & Row).Value = Cells(67, "P")
ws.Range("KC" & Row).Value = Cells(67, "AO")
ws.Range("KD" & Row).Value = Cells(67, "AS")
ws.Range("KE" & Row).Value = Cells(67, "AW")
ws.Range("KF" & Row).Value = Cells(67, "BA")
ws.Range("KG" & Row).Value = Cells(67, "BF")
ws.Range("KH" & Row).Value = Cells(68, "D")
ws.Range("KI" & Row).Value = Cells(68, "F")
ws.Range("KJ" & Row).Value = Cells(68, "P")
ws.Range("KK" & Row).Value = Cells(68, "AO")
ws.Range("KL" & Row).Value = Cells(68, "AS")
ws.Range("KM" & Row).Value = Cells(68, "AW")
ws.Range("KN" & Row).Value = Cells(68, "BA")
ws.Range("KO" & Row).Value = Cells(68, "BF")
ws.Range("KP" & Row).Value = Cells(69, "D")
ws.Range("KQ" & Row).Value = Cells(69, "F")
ws.Range("KR" & Row).Value = Cells(69, "P")
ws.Range("KS" & Row).Value = Cells(69, "AO")
ws.Range("KT" & Row).Value = Cells(69, "AS")
ws.Range("KU" & Row).Value = Cells(69, "AW")
ws.Range("KV" & Row).Value = Cells(69, "BA")
ws.Range("KW" & Row).Value = Cells(69, "BF")
ws.Range("KX" & Row).Value = Cells(70, "D")
ws.Range("KY" & Row).Value = Cells(70, "F")
ws.Range("KZ" & Row).Value = Cells(70, "P")
ws.Range("LA" & Row).Value = Cells(70, "AO")
ws.Range("LB" & Row).Value = Cells(70, "AS")
ws.Range("LC" & Row).Value = Cells(70, "AW")
ws.Range("LD" & Row).Value = Cells(70, "BA")
ws.Range("LE" & Row).Value = Cells(70, "BF")
ws.Range("FM" & Row).Value = wss.Range("F2").Value
ws.Range("FN" & Row).Value = wss.Range("F3").Value
ws.Range("FO" & Row).Value = wss.Range("F4").Value
ws.Range("FP" & Row).Value = wss.Range("F5").Value
ws.Range("FQ" & Row).Value = wss.Range("F6").Value
ws.Range("FR" & Row).Value = wss.Range("F7").Value
ws.Range("FS" & Row).Value = wss.Range("F8").Value
ws.Range("FT" & Row).Value = wss.Range("F9").Value
ws.Range("FU" & Row).Value = wss.Range("F10").Value
ws.Range("FV" & Row).Value = wss.Range("F11").Value
ws.Range("FW" & Row).Value = wss.Range("F12").Value
ws.Range("FX" & Row).Value = wss.Range("F13").Value
ws.Range("FY" & Row).Value = wss.Range("F14").Value
ws.Range("FZ" & Row).Value = wss.Range("F15").Value
ws.Range("GA" & Row).Value = wss.Range("F16").Value
ws.Range("GB" & Row).Value = wss.Range("F17").Value
ws.Range("GC" & Row).Value = wss.Range("F18").Value
ws.Range("GD" & Row).Value = wss.Range("F19").Value
ws.Range("GG" & Row).Value = Cells(72, "BB").Value
ws.Range("GH" & Row).Value = Cells(74, "BB").Value
ws.Range("GI" & Row).Value = "L"
End If
Next Row
Worksheets("MainMenu").Activate
ActiveWorkbook.Save
End Sub