macro to copy certain cells and paste to another worksheet

Alwinaz

Board Regular
Joined
Feb 7, 2012
Messages
201
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

Code:
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi Alwinaz,

Here's one way...
Code:
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Thank you JS411 it is doing what i want. I changed this part

Code:
.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?
 
Upvote 0
Here is a modified version that will copy no more than rows 4:17...

Code:
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
 
Upvote 0
Thanks, but now it is pasting b2 14 times. See Below
A B C D
[TABLE="width: 429"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"]Kleintjie[/TD]
[TD="width: 88, bgcolor: transparent"]Products[/TD]
[TD="width: 327, bgcolor: transparent"]Loreal Conditioner[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]350[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"]Procedure[/TD]
[TD="bgcolor: transparent"]Wash & Cut & Blow Med[/TD]
[TD="bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"]Procedure[/TD]
[TD="bgcolor: transparent"]Highlights[/TD]
[TD="bgcolor: transparent, align: right"]400[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"]Procedure[/TD]
[TD="bgcolor: transparent"]Style[/TD]
[TD="bgcolor: transparent, align: right"]300[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kleintjie[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

I only want column A to copy B2 for as many entries there is in column B.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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
Back
Top