copying cell H2 to a different cell on a different worksheet

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
ive got a work sheet and i want ot copy the contents of certain cells to a different worksheet

there are about 10 cells

whati want is cell H2 in job 1 to go to cell C2 of Job List
then Cell H2 in job 2 to go to cell C3 of job list
easy,

but i want it to do it for sheets not yet created as when im filling out job 1 job 2 hasnt been created, all the sheets will start with job then a number
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
You want to copy something that doesn't exist?:eek:
 

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
not quite LOL

i have a button and code so that when i click the button it prints, saves and creates a new worksheet ie job 2 ready for data entry,

somewhere in that process i would like it to copy the contents of certain cells from sheet job 1 to sheet job list,

but if i did

ActiveSheet Range("F6:S6").Select
Selection.Copy
Sheets("Job List").Select
Range("A2").Select
ActiveSheet.Paste

it will only copy that one cell, i want it to return back to job * and copy the next cell and so on,

but it must also be in the next avaliable blanck cell down on the job list

so where copying the contents in to cell A2 of the job list for Job 1, job 2 must be in A3, job 3 in A4 and so on
 

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
i have tried to butcher a code i found to this, but it still doesnt work, i thought it would.

any ideas

Code:
Sub Copytojoblist()

    Dim myRow
    Dim myBaris
    Dim Ws1 As Worksheet, Ws2 As Worksheet
     
    Set Ws1 = Workbooks("CAD Job sheet CRM Testing.xls").Sheets("Job 1")
    Set Ws2 = Workbooks("CAD Job sheet CRM Testing.xls").Sheets("Job List")
     
    myRow = Ws2.Range("A2").Value
     
    With Ws2
        .Range("A" & myRow) = Ws1.Range("F2").Value
        .Range("B" & myRow) = Ws1.Range("G2").Value
        .Range("C" & myRow) = Ws1.Range("H2").Value
        .Range("D:F" & myRow) = Ws1.Range("S2:U2").Value
        .Range("H:N" & myRow) = Ws1.Range("F6:S6").Value
        .Range("O:R" & myRow) = Ws1.Range("H4:K4").Value
        .Range("S:AA" & myRow) = Ws1.Range("F8:S8").Value
                
    myRow = myRow + 1
         
         
         .Range("A2").Value = myRow
         
        
           
         
    Set Ws1 = Nothing
    Set Ws2 = Nothing
    
    End With
    
    
    End Sub
 

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
i didnt explain my self to well
what im after is this:-

i want ot copy like this

Sheet Job 1 Cell F2 - Sheet Job List Cell A6
Sheet Job 1 Cell G2 - Sheet Job List Cell B6
Sheet Job 1 Cell H2 - Sheet Job List Cell C6
Sheet Job 1 Cell S2:U2 - Sheet Job List Cell D6:F6
Sheet Job 1 Cell F6:S6 - Sheet Job List Cell G6:N6
Sheet Job 1 Cell H4:K4 - Sheet Job List Cell O6:R6
Sheet Job 1 Cell F8:S8 - Sheet Job List Cell S6:AA6
Sheet Job 1 Cell AB10:AF10 - Sheet Job List Cell AB6:AE6
Sheet Job 1 Cell S4:U4 - Sheet Job List Cell AF6:AI6
Sheet Job 1 Cell V4:Y4 - Sheet Job List Cell AJ6:AM6
Sheet Job 1 Cell K59:R59 - Sheet Job List Cell AN6:AP6

that fine and reletivly easy.

the macro i have at the mo, save, copys the worksheet Job 1, and renames it job 2, so now i have 3 tabs in the work book, job 2 gets filled out and those cells need copying in to job list to, but 1 row down ie:

Sheet Job 2 Cell F2 - Sheet Job List Cell A7
Sheet Job 2 Cell G2 - Sheet Job List Cell B7
Sheet Job 2 Cell H2 - Sheet Job List Cell C7
Sheet Job 2 Cell S2:U2 - Sheet Job List Cell D7:F7
Sheet Job 2 Cell F6:S6 - Sheet Job List Cell G7:N7
Sheet Job 2 Cell H4:K4 - Sheet Job List Cell O7:R7
Sheet Job 2 Cell F8:S8 - Sheet Job List Cell S7:AA7
Sheet Job 2 Cell AB10:AF10 - Sheet Job List Cell AB7:AE7
Sheet Job 2 Cell S4:U4 - Sheet Job List Cell AF7:AI7
Sheet Job 2 Cell V4:Y4 - Sheet Job List Cell AJ7:AM7
Sheet Job 2 Cell K59:R59 - Sheet Job List Cell AN7:AP7

and repeat the process for Job 3, 4, 5........ 50 etc

i can email you my workbook if that helps

ive tried to use

activesheet(range("H4:k4").select
selection.copy

then copy to job list, but i cant work out how to get it ot go back to the latest job to select the next cell to copy

ican email my workbook to help if need be
 

MonkeyDeath

Board Regular
Joined
Jul 19, 2007
Messages
62
any ideas?

i still cant get it to work
 

JMAN

New Member
Joined
May 30, 2006
Messages
4
Can you attach a small sample? Are you copying between sheets in the same workbook?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What's the problem with the code you posted?

Perhaps this will work.
Code:
Sub Copytojoblist()
Dim myRow As Long
Dim WsDst As Worksheet
Dim WsSrc As Worksheet
    Set WsDst = Workbooks("CAD Job sheet CRM Testing.xls").Sheets("Job List")
    myRow = 6
     
    For Each WsSrc In Worksheets
    
        If WsSrc.Name <> WsDst.Name Then
            With Ws2
                .Range("A" & myRow) = WsSrc.Range("F2").Value
                .Range("B" & myRow) = WsSrc.Range("G2").Value
                .Range("C" & myRow) = WsSrc.Range("H2").Value
                .Range("D" & myRow & ":F" & myRow) = WsSrc.Range("S2:U2").Value
                .Range("H" & myRow & ":N" & myRow) = WsSrc.Range("F6:S6").Value
                .Range("O" & myRow & ":R" & myRow) = WsSrc.Range("H4:K4").Value
                .Range("S" & myRow & ":AA" & myRow) = WsSrc.Range("F8:S8").Value
                ' etc
                myRow = myRow + 1
            End With
        End If
        
    Next WsSrc
                       
End Sub
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,693
Latest member
BroTr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top