Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data across worksheets
Hello,
I've created a form very similar to the one on Contextures website (http://www.contextures.com/xlForm02.html). Only, mine is not as sophisticated as FormSheet.xls since it is a registration form for a meeting and some fields can be left blank (e.g. there are three address lines just in case but Address_2 and Address_3 doesn't need to be filled in). I don't need automatic entering of "Entered (time/date)" and "Entered By" columns either. In my Entry Form wks (equivalent of Input wks), the fields are not ligned up in one column as is the case with Input wks (all in Column D). Also in my Database wks (equivalent of PartsData wks), there are fields (columns) that are automatically filled in with VLOOKUP (e.g. country codes for phone are automatically filled in according to the data in "Country" field) and therefore a little manuevering is necessary to avoid copying into cells that are automatically filled in. How can I do this in an elegant way? The cells to be copied from Entry Form wks a C8,C10,C12,C14,C16,C18,C19,C20,C22,C24,C26,C28,D30 ,D32,C34,C38,C40,C42,C44,C46,C48,C51 and they would go to columns in Database wks: B,C,D,E,F,G,H,I,J,K,L,M,O,Q,R,T,U,V,W,X,Y,S NB: Column A is reserved, N and P are filled automatically in accordance with the data in M (comes originally from C28 of Entry Form wks) and S is filled by data in C51. Many thanks. -- Maki @ Canberra.AU |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data across worksheets
You may not need those date/time and entered by columns, but it is one way to
make sure that you can pick out a column that always has data in it. And that's important so that you can find the nextrow that should get data. I kept it in this code, but you can delete it and use a different column. Option Explicit Sub UpdateLogWorksheet() Dim HistoryWks As Worksheet Dim InputWks As Worksheet Dim NextRow As Long Dim iCtr As Long Dim myRng As Range Dim myCopy As Variant Dim myCols As Variant 'cells to copy from Input sheet - some contain formulas myCopy = Array("C8", "C10", "C12", "C14", "C16", _ "C18", "C19", "C20", "C22", "C24", _ "C26", "C28", "D30", "D32", "C34", _ "C38", "C40", "C42", "C44", "C46", _ "C48", "C51") myCols = Array("B", "C", "D", "E", "F", "G", "H", _ "I", "J", "K", "L", "M", "O", "Q", _ "R", "T", "U", "V", "W", "X", "Y", _ "S") If UBound(myCols) < UBound(myCopy) Then MsgBox "Design error!" Exit Sub End If Set InputWks = Worksheets("Input") Set HistoryWks = Worksheets("PartsData") With HistoryWks 'how do you find the next row, I used column A. NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'make sure something is in it! 'I added the date/time With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With For iCtr = LBound(myCopy) To UBound(myCopy) HistoryWks.Cells(NextRow, myCols(iCtr)).Value _ = InputWks.Range(myCopy(iCtr)).Value Next iCtr End With 'clear input cells that contain constants With InputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub Maki wrote: Hello, I've created a form very similar to the one on Contextures website (http://www.contextures.com/xlForm02.html). Only, mine is not as sophisticated as FormSheet.xls since it is a registration form for a meeting and some fields can be left blank (e.g. there are three address lines just in case but Address_2 and Address_3 doesn't need to be filled in). I don't need automatic entering of "Entered (time/date)" and "Entered By" columns either. In my Entry Form wks (equivalent of Input wks), the fields are not ligned up in one column as is the case with Input wks (all in Column D). Also in my Database wks (equivalent of PartsData wks), there are fields (columns) that are automatically filled in with VLOOKUP (e.g. country codes for phone are automatically filled in according to the data in "Country" field) and therefore a little manuevering is necessary to avoid copying into cells that are automatically filled in. How can I do this in an elegant way? The cells to be copied from Entry Form wks a C8,C10,C12,C14,C16,C18,C19,C20,C22,C24,C26,C28,D30 ,D32,C34,C38,C40,C42,C44,C46,C48,C51 and they would go to columns in Database wks: B,C,D,E,F,G,H,I,J,K,L,M,O,Q,R,T,U,V,W,X,Y,S NB: Column A is reserved, N and P are filled automatically in accordance with the data in M (comes originally from C28 of Entry Form wks) and S is filled by data in C51. Many thanks. -- Maki @ Canberra.AU -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying data across worksheets
Thank you, Dave. It works!
-- Maki @ Canberra.AU "Dave Peterson" wrote: You may not need those date/time and entered by columns, but it is one way to make sure that you can pick out a column that always has data in it. And that's important so that you can find the nextrow that should get data. I kept it in this code, but you can delete it and use a different column. Option Explicit Sub UpdateLogWorksheet() Dim HistoryWks As Worksheet Dim InputWks As Worksheet Dim NextRow As Long Dim iCtr As Long Dim myRng As Range Dim myCopy As Variant Dim myCols As Variant 'cells to copy from Input sheet - some contain formulas myCopy = Array("C8", "C10", "C12", "C14", "C16", _ "C18", "C19", "C20", "C22", "C24", _ "C26", "C28", "D30", "D32", "C34", _ "C38", "C40", "C42", "C44", "C46", _ "C48", "C51") myCols = Array("B", "C", "D", "E", "F", "G", "H", _ "I", "J", "K", "L", "M", "O", "Q", _ "R", "T", "U", "V", "W", "X", "Y", _ "S") If UBound(myCols) < UBound(myCopy) Then MsgBox "Design error!" Exit Sub End If Set InputWks = Worksheets("Input") Set HistoryWks = Worksheets("PartsData") With HistoryWks 'how do you find the next row, I used column A. NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row 'make sure something is in it! 'I added the date/time With .Cells(NextRow, "A") .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With For iCtr = LBound(myCopy) To UBound(myCopy) HistoryWks.Cells(NextRow, myCols(iCtr)).Value _ = InputWks.Range(myCopy(iCtr)).Value Next iCtr End With 'clear input cells that contain constants With InputWks On Error Resume Next With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts) .ClearContents Application.Goto .Cells(1) ', Scroll:=True End With On Error GoTo 0 End With End Sub Maki wrote: Hello, I've created a form very similar to the one on Contextures website (http://www.contextures.com/xlForm02.html). Only, mine is not as sophisticated as FormSheet.xls since it is a registration form for a meeting and some fields can be left blank (e.g. there are three address lines just in case but Address_2 and Address_3 doesn't need to be filled in). I don't need automatic entering of "Entered (time/date)" and "Entered By" columns either. In my Entry Form wks (equivalent of Input wks), the fields are not ligned up in one column as is the case with Input wks (all in Column D). Also in my Database wks (equivalent of PartsData wks), there are fields (columns) that are automatically filled in with VLOOKUP (e.g. country codes for phone are automatically filled in according to the data in "Country" field) and therefore a little manuevering is necessary to avoid copying into cells that are automatically filled in. How can I do this in an elegant way? The cells to be copied from Entry Form wks a C8,C10,C12,C14,C16,C18,C19,C20,C22,C24,C26,C28,D30 ,D32,C34,C38,C40,C42,C44,C46,C48,C51 and they would go to columns in Database wks: B,C,D,E,F,G,H,I,J,K,L,M,O,Q,R,T,U,V,W,X,Y,S NB: Column A is reserved, N and P are filled automatically in accordance with the data in M (comes originally from C28 of Entry Form wks) and S is filled by data in C51. Many thanks. -- Maki @ Canberra.AU -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying data from multiple worksheets | Excel Discussion (Misc queries) | |||
Copying data across different worksheets | Excel Discussion (Misc queries) | |||
Why is data automatically copying to all worksheets? | Excel Worksheet Functions | |||
Copying data within worksheets with lookups | Excel Discussion (Misc queries) | |||
Copying ranges of data between worksheets | Excel Worksheet Functions |