Copy a cell value from one worksheet to the first blank cell in column C on another worksheet & paste the value all the way down to the last used row

B

Bailey9

New Member
Joined
Nov 7, 2009
Messages
14
Hello
It has been a number of years since I have used VBA, and just can't figure out this SIMPLE solution.

What I have so far is:
Code
  1. ''''''''''''''''''''
  2. 'COPY THE FILE TYPES
  3. ''''''''''''''''''''
  4. Sheets("DO NOT DELETE").Select
  5. Range("A8").Select
  6. Selection.Copy
  7. Sheets("SUMMARY SHEET").Activate
  8. ActiveSheet.Range("C6000").Select
  9. ActiveSheet.Range("C6000").End(xlUp).Select
  10. ActiveCell.Offset(1, 0).Select
  11. ActiveSheet.PasteSpecial
  12. Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  13. :=False, Transpose:=False
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes

Copy a cell value from one worksheet to the first blank cell in column C on another worksheet & paste the value all the way down to the last used row IN THE WORKSHEET. This is a correction to above. Thank you.​

 
Upvote 0
Hi Balley9,

for the first question try
Code: 
    ''''''''''''''''''''
    'COPY THE FILE TYPES
    ''''''''''''''''''''
    Dim lngEnd As Long
    lngEnd = 6000
    With Sheets("SUMMARY SHEET")
      .Range(.Range("C" & lngEnd).End(xlUp).Offset(1, 0), .Range("C" & lngEnd)).Value = Sheets("DO NOT DELETE").Range("A8").Value
    End With
For the second question you would need to include which column to fill up and add a hint on how to decide about the last used row (SpecialCells?).

Ciao,
Holger
 
Upvote 0
Hi,

maybe this can help you with the second question:
Code: 
    Dim lngEnd As Long
    lngEnd = 6000
    With Sheets("DO NOT DELETE")
      Sheets("SUMMARY SHEET").Range("C" & lngEnd).End(xlUp).Offset(1, 0).Value = .Range("A8").Value
      .Range("B9").AutoFill Destination:=.Range(.Range("B9"), .Range("B" & lngEnd)), Type:=xlFillDefault
    End With
Ciao,
Holger
 
Upvote 0
You are so FAST!

Here's what I'm trying to do. I'm hoping by posting my whole code, it will help.

If not, please let me know to explain better.

My code:

Sub Summarize()
Dim btn As Long 'bottom row
Dim Rght As Long 'far right column
Dim LastRow As Long

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'CLEAR ALL DATA CLEAR ALL DATA CLEAR ALL DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' If AutoFiler is on, Turn Off '????????????
Sheets("SUMMARY SHEET").Select
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If

' Clear the Summary Sheet items, except headers
Sheets("SUMMARY SHEET").Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlUp


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'COPY THE ANNUAL REPORTS DATA COPY THE ANNUAL REPORTS DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''
'COPY THE STATES DATA
''''''''''''''''''''''''''''

' Select Annual Reports & Copy States
Sheets("Annual Report").Select

'Find the last row in column A
Range("A6000").End(xlUp).Select
btn = ActiveCell.Row

'Activate cell A
Range("A1").Activate
Rght = ActiveCell.Column

'Move down to row 3 in column A
Range(Cells(3, 1), Cells(btn, Rght)).Select

'Copy the STATES data
'Now copy the selected range
Selection.Copy
FileNm = ActiveWorkbook.Name
ThisWorkbook.Activate
Sheets("SUMMARY SHEET").Activate
ActiveSheet.Range("A6000").Select
ActiveSheet.Range("A6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

''''''''''''''''''''
'COPY THE DUE DATES
''''''''''''''''''''

' Select Annual Reports & Copy Due Daates
Sheets("Annual Report").Select

'Find the last row in column A
Range("A6000").End(xlUp).Select
btn = ActiveCell.Row

'Activate cell A
Range("J1").Activate
Rght = ActiveCell.Column

'Move down to row 3 in column J
Range(Cells(3, 10), Cells(btn, Rght)).Select

'Copy the DUE DATES data
'Now copy the selected range
Selection.Copy
FileNm = ActiveWorkbook.Name
ThisWorkbook.Activate
Sheets("SUMMARY SHEET").Activate
ActiveSheet.Range("B6000").Select
ActiveSheet.Range("B6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
' ActiveSheet.PasteSpecial
ActiveSheet.Range("B6000").Select
ActiveSheet.Range("B6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.PasteSpecial
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
''''''''''''''''''''
'COPY THE FILE TYPES
''''''''''''''''''''
Sheets("DO NOT DELETE").Select
Range("A7").Select
Selection.Copy
Sheets("SUMMARY SHEET").Activate
Range("C2").PasteSpecial
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C2").Copy Range("C3:C" & LastRow)


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'COPY THE FRANCHISE REPORTS DATA COPY THE FRANCHISE REPORTS DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''
'COPY THE STATES DATA
''''''''''''''''''''''''''''

' Select Annual Reports & Copy States
Sheets("Franchise").Select

'Find the last row in column A
Range("A6000").End(xlUp).Select
btn = ActiveCell.Row

'Activate cell A
Range("A1").Activate
Rght = ActiveCell.Column

'Move down to row 3 in column A
Range(Cells(3, 1), Cells(btn, Rght)).Select

'Copy the STATES data
'Now copy the selected range
Selection.Copy
FileNm = ActiveWorkbook.Name
ThisWorkbook.Activate
Sheets("SUMMARY SHEET").Activate
ActiveSheet.Range("A6000").Select
ActiveSheet.Range("A6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

''''''''''''''''''''
'COPY THE DUE DATES
''''''''''''''''''''

' Select Annual Reports & Copy Due Daates
Sheets("Franchise").Select

'Find the last row in column A
Range("A6000").End(xlUp).Select
btn = ActiveCell.Row

'Activate cell J
Range("J1").Activate
Rght = ActiveCell.Column

'Move down to row 3 in column J
Range(Cells(3, 10), Cells(btn, Rght)).Select

'Copy the DUE DATES data
'Now copy the selected range
Selection.Copy
FileNm = ActiveWorkbook.Name
ThisWorkbook.Activate
Sheets("SUMMARY SHEET").Activate
ActiveSheet.Range("B6000").Select
ActiveSheet.Range("B6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
' ActiveSheet.PasteSpecial
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
''''''''''''''''''''
'COPY THE FILE TYPES
''''''''''''''''''''
Sheets("DO NOT DELETE").Select
Range("A8").Select
Selection.Copy
Sheets("SUMMARY SHEET").Activate
ActiveSheet.Range("C6000").Select
ActiveSheet.Range("C6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.PasteSpecial
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Upvote 0
Hi hate that I am not a REAL coder, and when I get responses, they are so hard to read and understand. So please bare with me.
 
Upvote 0
I taught myself, so I struggle. Everyone's short coding is so brilliant, I just wish I could understand it.
 
Upvote 0
Hi Bailey9,

it takes a little time to go through the code. I´m working on it but you have some lines of code like
Code: 
FileNm = ActiveWorkbook.Name
ThisWorkbook.Activate
As I have not noticed any change of workbooks so far I just want to make sure that all sheets are located in the same workbook (so both ActiveWorkbook and ThisWorkbook refer to the same file)?

I will work on the code and come back with sort of a cleaned-up code and some comments as most of the Selects aren´t necessary.

Ciao,
Holger
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
187
DanteAmor
DanteAmor
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
309
Azamamir
A
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
105
willow1985
willow1985
C
  • Question
Calculations require application restart
Replies
2
Views
150
cpupartsplz
C
excelbytes
  • Solved
VBA Code To Copy Data Into Next Blank Column
Replies
5
Views
78
mumps
mumps

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

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