Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How To Automate Copy Certian Cells from Worksheet A to Certian Cells Worksheet B?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default How To Automate Copy Certian Cells from Worksheet A to Certian Cells Worksheet B?
I am currently not to experienced with VBA and have been trying to find Out
how to Automate Copying One Database to another

Currently I have a user database which is in Populate Database.xls I
currently Want to transfer the user information to the Another Options.xls
database
Now here are my current issues I want to basically convert this Big database
Populate Database.xls which contains specific user information for each
person on each row example
Range A1-A10 contain user information for bob
Range B1-B10 Contain user information for Susan
and so on for 100+ users
Furthermore I currently have to Archive each individual user in a pre
designed template with a worksheet for each individual user which is in
Another option.xls


I would currently like the code to be able to based on my first cell
selection to copy the information to the new workbook , Allow me to
elaborate to define exactly what I mean in this case


Example- I will select range ("b1") in workbook Populate Database.xls
and I will Run *this macro*
the macro will then know to copy the ranges of B1-B10 to the predefined
ranges in the workbook Another Option.xls this completes the macro
operations

ok now I select range("c1") in workbook Populate Database.xls and I will run
*this macro*
the macro will then know to copy the ranges of C1-C10 to the predefined
ranges in the workbook Another Option.xls this completes the macro




this is currently How far I got with the Code I think I did it Wrong your
Feedback would be Much appreciated




Sub AutomateChangeDirect()

Range("B15").Select
Selection.Copy
Windows("Another Option.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("C15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("G15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=26
Range("D30").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("H15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
Range("D22").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("I15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("J15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=4
Range("D25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
Range("B11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("K15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=17
Range("D28").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("M15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D29").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("O15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=-1
Range("D26").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.LargeScroll Down:=1
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 10
ActiveWindow.SmallScroll ToRight:=2
Range("P15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=-11
Windows("Populate Database.xls").Activate
Windows("Another Option.xls").Activate
Range("D17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("Q15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("R15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Windows("Populate Database.xls").Activate
Windows("Another Option.xls").Activate
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Windows("Another Option.xls").Activate
Windows("Populate Database.xls").Activate
Range("S15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=4
Range("D21").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

End Sub


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default How To Automate Copy Certian Cells from Worksheet A to Certian Cel
Clarence, I did not build a model for this to test it, so you
will have to test it on a copy of your files. If it does not run
OK then post back with what problems come up. Watch the
word wrap when you copy this over to your code module. You
might have to move some code up to the correct line.

Sub cpyrng()
lc1 = WorkBooks("Populate Database.xls").Worksheets(1).Cells(1,
Columns.Count).End(xlToLeft).Column
Wk1 = Workbooks("Populate Database.xls").Worksheets(1)
Wk2 = Workbooks("Another Option.xls").Worksheets(1)
Counter = 1
Do
lc2 = Workbooks("Another Option.xls").Worksheets(1).Cells(1,
Columns.Count).End(xlToLeft).Column
If Wk1.Cells(1, Counter) < "" Then
Wk1.Range(Cells(1, Counter), Cells(10, Counter)).Copy Wk2.Range(Cells(1,
lc2 + 1))
Counter = Counter + 1
End If
Loop Until Counter = lc1 + 1
End Sub

"Clarence Jackson" wrote:

I am currently not to experienced with VBA and have been trying to find Out
how to Automate Copying One Database to another

Currently I have a user database which is in Populate Database.xls I
currently Want to transfer the user information to the Another Options.xls
database
Now here are my current issues I want to basically convert this Big database
Populate Database.xls which contains specific user information for each
person on each row example
Range A1-A10 contain user information for bob
Range B1-B10 Contain user information for Susan
and so on for 100+ users
Furthermore I currently have to Archive each individual user in a pre
designed template with a worksheet for each individual user which is in
Another option.xls


I would currently like the code to be able to based on my first cell
selection to copy the information to the new workbook , Allow me to
elaborate to define exactly what I mean in this case


Example- I will select range ("b1") in workbook Populate Database.xls
and I will Run *this macro*
the macro will then know to copy the ranges of B1-B10 to the predefined
ranges in the workbook Another Option.xls this completes the macro
operations

ok now I select range("c1") in workbook Populate Database.xls and I will run
*this macro*
the macro will then know to copy the ranges of C1-C10 to the predefined
ranges in the workbook Another Option.xls this completes the macro




this is currently How far I got with the Code I think I did it Wrong your
Feedback would be Much appreciated




Sub AutomateChangeDirect()

Range("B15").Select
Selection.Copy
Windows("Another Option.xls").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("C15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("G15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=26
Range("D30").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("H15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 20
Range("D22").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("I15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("J15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=4
Range("D25").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
Range("B11").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("K15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=17
Range("D28").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("M15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D29").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("O15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=-1
Range("D26").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.LargeScroll Down:=1
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 10
ActiveWindow.SmallScroll ToRight:=2
Range("P15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=-11
Windows("Populate Database.xls").Activate
Windows("Another Option.xls").Activate
Range("D17").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
Range("Q15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Range("D19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Range("R15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
Windows("Populate Database.xls").Activate
Windows("Another Option.xls").Activate
Range("D20").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Windows("Populate Database.xls").Activate
ActiveWindow.SmallScroll ToRight:=2
Windows("Another Option.xls").Activate
Windows("Populate Database.xls").Activate
Range("S15").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Another Option.xls").Activate
ActiveWindow.SmallScroll Down:=4
Range("D21").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False

End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I can't change font color in certian cells. bcitygirl Excel Discussion (Misc queries) 2 April 5th 23 01:15 PM
Is it posible to jump to or search for a certian worksheet? Cheryl Excel Worksheet Functions 2 August 7th 09 04:09 PM
taking certian data from worksheet one to worksheet 2 [email protected] Excel Worksheet Functions 1 September 12th 06 09:01 AM
hiding cells with a certian type of formatting Waynef Excel Discussion (Misc queries) 1 August 23rd 05 07:35 PM
Sorting/Adding certian cells Chance[_2_] Excel Programming 1 August 25th 03 12:03 AM


All times are GMT +1. The time now is 04:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement