macro to copy certain cells and paste to another worksheet

A

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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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
Jerk24 said:
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?
Click to expand...

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
You must log in or register to reply here.

Similar threads

brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
346
jolivanes
jolivanes
I
  • Solved
Subscript out of range when copying values to another sheet
Replies
9
Views
184
ipbr21054
I
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
1K
Joe4
Joe4
M
  • Question Question
Macro code to copy & paste cell data
Replies
1
Views
148
Maverick27
M
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
196
kbk
K

Forum statistics

Threads
1,223,445
Messages
6,172,177
Members
452,446
Latest member
walkman99

Share this page

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