#1
|
|||
|
|||
Shift Excel Cell after executing a macro for second time
Hello All,
I've written a (below)macro that pulls data from the sql server 2008 r2. My issue is when the user runs the macro for first time by entering Jobnumber (say J0001) excel puts data on the spreadsheet starting from cell "A1" which is fine. The issue here is, when the user runs the macro for the second time by entering the jobnumber (say J0002), excel puts the data for Jobnumber (J0002) on cell "A1" and shifts the cells for J0001(first job) to cell "F" instead of moving down. How can I shift the previous entry down in the spreadsheet with the latest entry on top? Here is my macro and attachment: Code:
Sub Task() Dim sqlstring As String Dim connstring As String Dim Strcode As String Strcode = Trim(InputBox("Please enter a Job #", "Task history")) sqlstring = "select distinct m.JobNumber , cast(m.ExpectedDate as DATE) 'Ship Date' ,m.CustLongName 'Customer' & _ " from ArchiveJobHeader m left join AuxiliaryInfoFile af (nolock) on af.jobnumber=m.jobnumber & _ " where m.JobNumber = '" & Trim(Strcode) & "'" & _ " order by 'Resulttime'" connstring = "ODBC;DSN=SQLDSN;UID=test;PWD=test123" Dim thisQT As QueryTable Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("a1", "a1000")) thisQT.BackgroundQuery = False thisQT.Sql = sqlstring thisQT.Refresh End Sub |
#2
|
|||
|
|||
HI,
Have the file Destination go to another part of the sheet and then you copy the data to "A1" and have it insert the copied data and at the same time move the file down. This is a recorded macro. But it may help. Code:
Sub Macro3() Range("H1:H3").Copy Range("A1").Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False End Sub |
#3
|
|||
|
|||
Ok. Just waited my time. User found solution at:
http://www.vbforums.com/showthread.p...107&viewfull=1 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete empty cell and shift up | Linh | Word Tables | 1 | 09-21-2014 01:52 PM |
Excel 2013 – need help extracting date and time from text cell plus more. | Art Mann | Excel | 1 | 07-18-2014 09:55 PM |
Can anyone here tweek this macro for renaming Excel files based on a cell's contents? | chrisd2000 | Excel Programming | 6 | 07-01-2014 01:53 PM |
Shift cell right | gsrikanth | Excel Programming | 2 | 01-17-2012 11:29 PM |
Cell formatting for time - Excel 2010 | PomDave | Excel | 5 | 09-05-2011 10:49 PM |