macro to copy certain cells and paste to another worksheet

[Alwinaz]

Hi there

I am using the following macro which one of the board member helped me with to copy and paste certain info from one sheet to another. The macro I am using is

Sub test()
With Sheets("Invoice")
    .Range("A4:C17").Copy
    Sheets("Transactions").Range("b" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With


End Sub

Is it possible to copy the value of B2 in the sheet (Invoice) as well and paste it for as many entries there are in the rows 4:17 in the sheet "Transactions". For example say i have items in row 4:10 when it copies the info to the sheet Transactions then it must copy the entry that is in B2 on sheet Invoice also in column a 4:10. Hope i make sense.

 

[Jerry Sullivan]

Hi Alwinaz,

Here's one way...

Sub test2()
   Dim sStamp As String
   Dim lRow1 As Long, lRow2 As Long
   
   With Sheets("Invoice")
      sStamp = .Range("B2").Text
      .Range("A4:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Copy
   End With
   
   With Sheets("Transactions")
      lRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
      .Range("B" & lRow1 + 1).PasteSpecial Paste:=xlPasteValues
      lRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
      .Range("A" & lRow1 + 1).Resize(lRow2 - lRow1).Value = sStamp
   End With

End Sub

 

[Jerk24]

I have that same thing I need help with'

I have a Sheet called Master... which is a report with 9 task. is there a way to move each task to another sheet that is named for that task... the names are shortened to the first word or two because of size restrictions.

the task name is in D:D (the lengh of the report is longer some weeks)
i need the whole row to copy over

any ideas?

 

[Jerry Sullivan]

I have that same thing I need help with'

I have a Sheet called Master... which is a report with 9 task. is there a way to move each task to another sheet that is named for that task... the names are shortened to the first word or two because of size restrictions.

the task name is in D:D (the lengh of the report is longer some weeks)
i need the whole row to copy over

any ideas?

Probably best to start a new thread for this. I'll be glad to help you if someone else doesn't provide a solution.

 

[Jerk24]

Ok. thank you

 

[Alwina]

Thank you JS411 it is doing what i want. I changed this part

.Range("A4:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).Copy[\Code]

to

[Code].Range("A4:C17" & .Cells(.Rows.Count, "B").End(xlUp).Row).Copy[\Code]
as i dont want it to copy anything after row 17, but it still copies down, How can i change this to not copy any futher than row c17?

 

[Jerry Sullivan]

Here is a modified version that will copy no more than rows 4:17...

Sub test3()
   Dim sStamp As String
   Dim nRows As Long, lRow As Long
   
   With Sheets("Invoice")
      sStamp = .Range("B2").Text
      nRows = .Cells(.Rows.Count, "B").End(xlUp).Row - 3
      If nRows < 1 Then Exit Sub
      If nRows > 17 - 3 Then nRows = 14
      .Range("A4").Resize(nRows, 3).Copy
   End With
   
   With Sheets("Transactions")
      lRow = .Cells(.Rows.Count, "B").End(xlUp).Row
      .Range("B" & lRow + 1).PasteSpecial Paste:=xlPasteValues
      .Range("A" & lRow + 1).Resize(nRows).Value = sStamp
   End With

End Sub

 

[Alwina]

Thanks, but now it is pasting b2 14 times. See Below
A B C D

Kleintjie	Products	Loreal Conditioner	350
Kleintjie	Procedure	Wash & Cut & Blow Med	200
Kleintjie	Procedure	Highlights	400
Kleintjie	Procedure	Style	300
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie
Kleintjie

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 245pt; mso-width-source: userset; mso-width-alt: 11958" width=327><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY>
</TBODY>

I only want column A to copy B2 for as many entries there is in column B.

 

[Jerry Sullivan]

That implies that even though Sheet Invoice B8:B14 appear blank, there are either formulas in those cells or data in cells below B17?
Could you have either or both of those scenarios?

 

[Alwinaz]

i have a formula with an if statement that makes "" if no intry in column A