Copying Form cells (WS1) to Database (WS2)
 

I need a macro to copy cells from a form created in Excel 2003 (Worksheet 1)
to a database (Worksheet 2). There is no structured range of data in the
form, however i need it summarised on 1 row in Worksheet 2.

I have recored a macro to export the data from WS1 to WS2 however i know
this is going to overwrite the previous run. Therefore everytime the macro is
run i need it to fill on the row below or next available row.

Hope someone can help. Thanks

Current Macro below,

Sub Info_Copy()
'
' Info_Copy Macro
' Macro recorded 12/03/2009 by ...
'

'
Range("G3:N3").Select
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("C4:J4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("Y3:AF3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("K4:R4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("G5:AF5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("S4:AR4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=21
Range("Z48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("AS4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("Z50").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("AT4").Select
ActiveSheet.Paste
Range("AS4").Select
Sheets("Sheet1").Select
End Sub

Copying Form cells (WS1) to Database (WS2)
 

I'm not sure I understand completely, but maybe...

Sub Info_Copy()

Dim fWks as worksheet 'from worksheet
dim tWks as worksheet 'to worksheet
Dim DestCell as range

set fwks = worksheets("sheet1")
set twks = worksheets("sheet1 (2)")

with twks
'destination is under the last used cell in column A
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

with twks
destcell.value = .range("AS40").value
'next cell to the right
destcell.offset(0,1).value = .range("x99").value
'column C
destcell.offset(0,2).value = .range("g1234").value
'and so forth
end with
End Sub

It does rely on column A always having data in it when the row is used.

MJS wrote:

I need a macro to copy cells from a form created in Excel 2003 (Worksheet 1)
to a database (Worksheet 2). There is no structured range of data in the
form, however i need it summarised on 1 row in Worksheet 2.

I have recored a macro to export the data from WS1 to WS2 however i know
this is going to overwrite the previous run. Therefore everytime the macro is
run i need it to fill on the row below or next available row.

Hope someone can help. Thanks

Current Macro below,

Sub Info_Copy()
'
' Info_Copy Macro
' Macro recorded 12/03/2009 by ...
'

'
Range("G3:N3").Select
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("C4:J4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("Y3:AF3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("K4:R4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("G5:AF5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("S4:AR4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveWindow.SmallScroll Down:=21
Range("Z48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("AS4").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("Z50").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1 (2)").Select
Range("AT4").Select
ActiveSheet.Paste
Range("AS4").Select
Sheets("Sheet1").Select
End Sub



--

Dave Peterson